SQL: Function to highlight text that has been searched for

The SQL Server function below 1Please ensure you test any script taken from my website on a test/development machine, before running on a production server., can be used to highlight text (while preserving the case) that has been searched for within a web page.

For example:

Source: “This is the source text”
Search: “source”
Function call:

SELECT dbo.fnHighlightSearchText('source', 'This is the source text')

Return value:

This is the source text

Web page output: This is the source text

CREATE FUNCTION [dbo].[fnHighlightSearchText]
(
	@SearchText VARCHAR(100),
	@SourceText VARCHAR(1000)
)
RETURNS VARCHAR(1000)
AS
BEGIN
	DECLARE @ReturnText VARCHAR(1000)
	DECLARE @Position INT
	DECLARE @Start INT
	DECLARE @Continue BIT
	
	SET @Continue = 1
	SET @Start = 1
	SET @ReturnText = ''

	WHILE @Continue = 1
	BEGIN
		-- Get the position of the search text within the source text.
		-- @Start is used to identify the starting point of the next search.
		SET @Position = CHARINDEX(@SearchText, @SourceText, @Start)
		IF @Position > 0
		BEGIN
			SET @ReturnText = @ReturnText + SUBSTRING(@SourceText, @Start, @Position - @Start)
			--The SPAN style can be changed as required.
			SET @ReturnText	= @ReturnText + ''
			SET @ReturnText = @ReturnText + SUBSTRING(@SourceText, @Position, LEN(@SearchText))
			SET @ReturnText = @ReturnText + ''
			SET @Start = @Position + LEN(@SearchText)
			IF @Start = LEN(@SourceText)
			BEGIN
				--If the next starting point is at the end of the source text, then
				--set @Continue to zero, thereby ending the while loop.
				SET @Continue = 0
			END
		END
		ELSE
		BEGIN
			IF @ReturnText = ''
			BEGIN
				--If the search text is not found, just return the source text
				SET @ReturnText = @SourceText
			ELSE
			BEGIN
				IF @Start < LEN(@SourceText)
				BEGIN
					--If the start position is before the end of the source text,
					--add the remaining of the source text to the Return Text.
					SET @ReturnText = @ReturnText + SUBSTRING(@SourceText, @Start, LEN(@SourceText) - @Start + 1)
				END
			END
			--End the While loop by setting @Continue to 0.
			SET @Continue = 0
		END
	END

	RETURN @ReturnText
END

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.