How to highlight searching words in text

Here is quick example how to highlight searching words using full text search in TSQL. It also avoids highlighting stop words
Author: Andrew Butenko
Create date: 14-01-2015
Description: highlight searching words in text including forms and excluding stop words

DECLARE @contents VARCHAR(MAX) = '123 defence ARMV-AT. NAVY-as, MC. - STRAIGHY, NEAR SOLDER CONTACTS. AN TYPE buiion 3: ', @searchTerm varchar(255) = 'defence navy'
SELECT [dbo].[HighLightSearchWords](@contents,@searchTerm)
CREATE FUNCTION [dbo].[HighLightSearchWords]
@contents VARCHAR(8000),
@searchTerm varchar(255)
DECLARE @retval VARCHAR(8000) = ''
;WITH Forms AS (
SELECT DISTINCT prs.display_term
FROM sys.dm_fts_parser(N'FORMSOF(FREETEXT, "' + @searchTerm + '")', 1033, 0, 1) prs
LEFT OUTER JOIN [sys].[fulltext_stopwords] stw ON prs.display_term COLLATE Latin1_General_CI_AS = stw.[stopword] COLLATE Latin1_General_CI_AS
WHERE stw.[stopword] IS NULL
, Matches(display_term, start_pos) AS(
SELECT display_term, CHARINDEX(display_term, @contents, 0) as start_pos
FROM Forms
WHERE CHARINDEX(display_term, @contents, 0)>0
SELECT m.display_term, CHARINDEX(m.display_term, @contents, m.start_pos+LEN(m.display_term)) as start_pos
FROM Matches AS m
WHERE CHARINDEX(m.display_term, @contents, m.start_pos+LEN(m.display_term))>0)
, Results AS(
SELECT ROW_NUMBER() OVER(ORDER BY start_pos) AS Id, start_pos, MAX(LEN(display_term)) as term_length
FROM Matches
GROUP BY start_pos)
SELECT @retval += (CASE l.Id WHEN 1 THEN SUBSTRING(@contents, 1, l.start_pos-1) ELSE '' END) --left
+'' + SUBSTRING(@contents, l.start_pos, l.term_length)+'' --middle
+(CASE WHEN r.start_pos - (l.start_pos + l.term_length + 1) > 0 THEN SUBSTRING(@contents, l.start_pos + l.term_length +1, r.start_pos - (l.start_pos + l.term_length + 1) )
ELSE SUBSTRING(@contents, l.start_pos + l.term_length, LEN(@contents))
END) --right
FROM Results l
LEFT OUTER JOIN Results r ON l.Id = r.Id - 1
ORDER BY l.start_pos
RETURN @retval


About Andrew Butenko
This entry was posted in SQL and tagged , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s