SQL: Function to highlight text that has been searched for

The SQL Server function below 1, 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
  1. Please ensure you test any script taken from my website on a test/development machine, before running on a production server.

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.