Hit Highlight for SQL Server Full Text Search

SQL Server Full Text Search finds documents but does not help you to build highlighted extracts of documents. If you want you still can do it yourself. The algorithm is the following:

1. find documents using Full Text Search
2. load contents of each document
3. parse the search phrase into words
4. build extract by finding search terms in the document body

The function can look like this:

CREATE FUNCTION [dbo].[HighLightSearch](@contents NVARCHAR(MAX), 
  @searchTerm NVARCHAR(4000), @style NVARCHAR(4000), @maxLen INT)
RETURNS NVARCHAR(MAX)
AS
BEGIN
    DECLARE @c NCHAR(1)
    DECLARE @len INT = 0
    DECLARE @l INT = 0
    DECLARE @p INT = 0
    DECLARE @prevPos INT = 0
    DECLARE @margin INT
    DECLARE @term NVARCHAR(4000)
    DECLARE @retval NVARCHAR(MAX) = ''
    
    DECLARE @positions TABLE
    (
        S INT,
        L INT
    )

    -- find all occurrences of the search term

    DECLARE cur1 CURSOR FOR
    SELECT display_term FROM sys.dm_fts_parser(
       N'FORMSOF(FREETEXT, "' + @searchTerm + '")', 1033, 0, 1)
    
    OPEN cur1
    FETCH NEXT FROM cur1 INTO @term

    WHILE @@FETCH_STATUS = 0
    BEGIN
        WHILE 1 = 1
        BEGIN
            SET @p = CHARINDEX(@term, @contents, @p)
            IF @p <= 0 BREAK
            
            SET @l = LEN(@term)
            
            IF @p > 0 BEGIN
                SET @c = SUBSTRING(@contents, @p - 1, 1)
                IF @c <> ' ' AND @c <> NCHAR(9) AND 
                   @c <> NCHAR(13) AND @c <> NCHAR(10) BREAK
            END
            
            INSERT INTO @positions (S, L) VALUES(@p, @l)
            SET @p = @p + LEN(@term)
        END
        
        FETCH NEXT FROM cur1 INTO @term   
    END   

    CLOSE cur1
    DEALLOCATE cur1
    
    -- build the result string
    
    DECLARE cur2 CURSOR FOR
    SELECT S, MAX(L)
    FROM @positions
    GROUP BY S
    ORDER BY S 
    
    SET @margin = LOG(@maxLen) * 5
    IF @margin > @maxLen / 4 SET @margin = @maxLen / 4
    SELECT @prevPos = MIN(S) - @margin FROM @positions

    OPEN cur2
    FETCH NEXT FROM cur2 INTO @p, @l

    WHILE @@FETCH_STATUS = 0 AND @len < @maxLen
    BEGIN
        SET @retval = @retval + SUBSTRING(@contents, @prevPos, @p - @prevPos)
        SET @retval = @retval + '<span style="' + @style + '">' + SUBSTRING(@contents, @p, @l)  + '</span>'
        SET @len = @len + @p - @prevPos + @l
        SET @prevPos = @p + @l
        
        FETCH NEXT FROM cur2 INTO @p, @l
    END   

    CLOSE cur2
    DEALLOCATE cur2

    SET @margin = LOG(@maxLen) * 5
    IF @margin + @len < @maxLen SET @margin = @maxLen - @len
    IF @margin > 0 SET @retval = @retval + SUBSTRING(@contents, @prevPos, @l)

    RETURN '...' + @retval + '...'
END

Being called with the following arguments...

SELECT dbo.HighLightSearch(
'40 Liters glass fish tank with lid, hangon filter and air pump. 
' I will throw in 6 guppies for you to start up the tank.  Great for kids, beginner or as a present.
The tank has no leaks or scratches, sell due to upgraded to a bigger tank.
Please note, it doesn''t come with a heater.
If you have any questions, please contact me.',
'leak or scratch',
'background-color:yellow;font-weight:bold',
50)

... it returns the following:

...tank has no leaks or scratches, sell du...

And this is how you can use this function in stored proc:

ALTER PROCEDURE [dbo].[Search]
(
    @searchTerm nvarchar(100),
    @style nvarchar(100) = 'font-weight:bold; background-color:yellow',
    @maxLen INT = 100
)
AS 
BEGIN
    SELECT 
        Id, 
        Name,
        dbo.HighLightSearch(Description, @searchTerm, @style, @maxLen) AS Extract 
    FROM Documents
    WHERE FREETEXT (Description, @searchTerm);
END