Click here to Skip to main content
13,198,322 members (54,579 online)
Click here to Skip to main content
Add your own
alternative version

Tagged as

Stats

11.9K views
5 bookmarked
Posted 20 Jul 2013

Hit Highlight for SQL Server Full Text Search

, 20 Jul 2013
Rate this:
Please Sign up or sign in to vote.
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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

m_kramar
Australia Australia
No Biography provided

You may also be interested in...

Pro
Pro

Comments and Discussions

 
QuestionNegated Terms Pin
tfranzese1-May-15 17:53
membertfranzese1-May-15 17:53 
Questionenhancements for accents incensitive and case incensitive Pin
Member 61912725-Feb-15 3:21
memberMember 61912725-Feb-15 3:21 
QuestionThis only works if content is stored in clear text Pin
bw21127330-Oct-13 3:19
memberbw21127330-Oct-13 3:19 
GeneralMy vote of 1 Pin
Marius Bancila12-Aug-13 3:01
memberMarius Bancila12-Aug-13 3:01 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.171020.1 | Last Updated 20 Jul 2013
Article Copyright 2013 by m_kramar
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid