--**************************************
--     
-- Name: Google Display
-- Description:searches a keyword in a t
--     ext column and displays as a google sear
--     ch format
-- By: chandra sekhar
--
-- Inputs:@str -- text
@keyword-- keyword
--
-- Returns:displays the result as a goog
--     le format
--
--This code is copyrighted and has-- limited warranties.Please see http://
--     www.Planet-Source-Code.com/vb/scripts/Sh
--     owCode.asp?txtCodeId=773&lngWId;=5--for details.--**************************************
--     

/******************************************************************************************************
/******************************************************************************************************
** function Name	: fnSrchFormat
** Created BY		: p.c sekhar 		 
** Date			: 09/29/2003
** Input parameters : @str -- text
			 @srch -- keyword
** Output parameters 	: ""
** RETURN value		:0/1 SUCCESS/FAILURE
** Result		:resultset WITH the keyword IN bold
** Purpose		: FOR most OF the asp applications IF you want TO find a specific keyword
			 THEN this function will gives you AS an html output
/*
-- sample execution 
DECLARE @str varchar(5000),@keyWord varchar(200)
SET @str = 'Microsoft SQL Server 2000 extends the performance, reliability, quality, AND ease-of-use of Microsoft SQL Server version 7.0. Microsoft SQL Server 2000 includes several new features that make it an excellent DATABASE platform for large-scale online transactional processing (OLTP), data warehousing, and e-commerce applications.'
SET @keyWord = 'sql'
SELECT dbo.fnSrchFormat (@str ,@keyWord) 
*/
CREATE function fnSrchFormat
(
@str varchar(5000),@srch varchar(200)
)
returns VARCHAR(5000)
as
    BEGIN
    DECLARE @iPos int 
    DECLARE @strRes varchar(8000)
    DECLARE @iStrt int 
    DECLARE @sformatted varchar(1200)
    SET @iStrt= 0
    SET @sformatted = ''
    SET @iPos = patindex(@srch,@str)
    WHILE NOT @iStrt >= len(@str) + 1
    	BEGIN 
    		IF substring(@str,@iStrt,len(@srch)) = @srch 
    SET @sformatted = @sformatted + '....' + replace(substring(@str,@iStrt -5 ,(@iStrt + 5)),@srch,'<B>' + @srch + '</B>')+ '....'
    	SET @iStrt = @iStrt + 1
    	CONTINUE
    	END 
    RETURN @sformatted 
END