<p>We have a web application that uses SQL Server 2008 as the database. Our users are able to do full-text searches on particular columns in the database. SQL Server's full-text functionality does not seem to provide support for hit highlighting. Do we need to build this ourselves or is there perhaps some library or knowledge around on how to do this? </p>

<p>BTW the application is written in C# so a .Net solution would be ideal but not necessary as we could translate.</p>

[{'answer_id': 74932, 'author': 'WIDBA', 'author_id': 10868, 'author_profile': 'https://Stackoverflow.com/users/10868', 'pm_score': 1, 'selected': False, 'text': '<p>You might be missing the point of the database in this instance.  Its job is to return the data to you that satisfies the conditions you gave it.  I think you will want to implement the highlighting probably using regex in your web control.</p>\n\n<p>Here is something a quick search would reveal.</p>\n\n<p><a href="http://www.dotnetjunkies.com/PrintContent.aspx?type=article&amp;id=195E323C-78F3-4884-A5AA-3A1081AC3B35" rel="nofollow noreferrer">http://www.dotnetjunkies.com/PrintContent.aspx?type=article&amp;id=195E323C-78F3-4884-A5AA-3A1081AC3B35</a></p>\n'}, {'answer_id': 127080, 'author': 'xnagyg', 'author_id': 2622295, 'author_profile': 'https://Stackoverflow.com/users/2622295', 'pm_score': 1, 'selected': False, 'text': '<p>Some details:</p>\n\n<pre><code>            search_kiemeles=replace(lcase(search),"""","")\n            do while not rs.eof  \'The search result loop\n                hirdetes=rs("hirdetes")\n                data=RegExpValueA("([A-Za-zöüóőúéáűíÖÜÓŐÚÉÁŰÍ0-9]+)",search_kiemeles)   \'Give back all the search words in an array, I need non-english characters also\n                For i=0 to Ubound(data,1)\n                    hirdetes = RegExpReplace(hirdetes,"("&amp;NoAccentRE(data(i))&amp;")","&lt;em&gt;$1&lt;/em&gt;")\n                Next\n                response.write hirdetes\n                rs.movenext\n            Loop\n            ...\n</code></pre>\n\n<p>Functions</p>\n\n<pre><code>\'All Match to Array\nFunction RegExpValueA(patrn, strng)\n    Dim regEx\n    Set regEx = New RegExp   \' Create a regular expression.\n    regEx.IgnoreCase = True   \' Set case insensitivity.\n    regEx.Global = True\n    Dim Match, Matches, RetStr\n    Dim data()\n    Dim count\n    count = 0\n    Redim data(-1)  \'VBSCript Ubound array bug workaround\n    if isnull(strng) or strng="" then\n        RegExpValueA = data\n        exit function\n    end if\n    regEx.Pattern = patrn   \' Set pattern.\n    Set Matches = regEx.Execute(strng)   \' Execute search.\n    For Each Match in Matches   \' Iterate Matches collection.\n        count = count + 1\n        Redim Preserve data(count-1)\n      data(count-1) = Match.Value\n    Next\n    set regEx = nothing\n    RegExpValueA = data\nEnd Function\n\n\'Replace non-english chars\nFunction NoAccentRE(accent_string)\n    NoAccentRE=accent_string\n    NoAccentRE=Replace(NoAccentRE,"a","§")\n    NoAccentRE=Replace(NoAccentRE,"á","§")\n    NoAccentRE=Replace(NoAccentRE,"§","[aá]")\n    NoAccentRE=Replace(NoAccentRE,"e","§")\n    NoAccentRE=Replace(NoAccentRE,"é","§")\n    NoAccentRE=Replace(NoAccentRE,"§","[eé]")\n    NoAccentRE=Replace(NoAccentRE,"i","§")\n    NoAccentRE=Replace(NoAccentRE,"í","§")\n    NoAccentRE=Replace(NoAccentRE,"§","[ií]")\n    NoAccentRE=Replace(NoAccentRE,"o","§")\n    NoAccentRE=Replace(NoAccentRE,"ó","§")\n    NoAccentRE=Replace(NoAccentRE,"ö","§")\n    NoAccentRE=Replace(NoAccentRE,"ő","§")\n    NoAccentRE=Replace(NoAccentRE,"§","[oóöő]")\n    NoAccentRE=Replace(NoAccentRE,"u","§")\n    NoAccentRE=Replace(NoAccentRE,"ú","§")\n    NoAccentRE=Replace(NoAccentRE,"ü","§")\n    NoAccentRE=Replace(NoAccentRE,"ű","§")\n    NoAccentRE=Replace(NoAccentRE,"§","[uúüű]")\nend function\n</code></pre>\n'}, {'answer_id': 619964, 'author': 'Ishmael', 'author_id': 8930, 'author_profile': 'https://Stackoverflow.com/users/8930', 'pm_score': 2, 'selected': False, 'text': '<p>It looks like you could parse the output of the new <a href="http://msdn.microsoft.com/en-us/library/cc280463.aspx" rel="nofollow noreferrer">SQL Server 2008 stored procedure sys.dm&#95;fts&#95;parser</a> and use regex, but I haven\'t looked at it too closely.</p>\n'}, {'answer_id': 3338113, 'author': 'Shagglez', 'author_id': 369166, 'author_profile': 'https://Stackoverflow.com/users/369166', 'pm_score': 3, 'selected': True, 'text': '<p>Expanding on Ishmael\'s idea, it\'s not the final solution, but I think it\'s a good way to start. </p>\n\n<p>Firstly we need to get the list of words that have been retrieved with the full-text engine:  </p>\n\n<pre><code>declare @SearchPattern nvarchar(1000) = \'FORMSOF (INFLECTIONAL, " \' + @SearchString + \' ")\' \ndeclare @SearchWords table (Word varchar(100), Expansion_type int)\ninsert into @SearchWords\nselect distinct display_term, expansion_type\nfrom sys.dm_fts_parser(@SearchPattern, 1033, 0, 0)\nwhere special_term = \'Exact Match\'\n</code></pre>\n\n<p>There is already quite a lot one can expand on, for example the search pattern is quite basic; also there are probably better ways to filter out the words you don\'t need, but it least it gives you a list of stem words etc. that would be matched by full-text search.</p>\n\n<p>After you get the results you need, you can use RegEx to parse through the result set (or preferably only a subset to speed it up, although I haven\'t yet figured out a good way to do so). For this I simply use two while loops and a bunch of temporary table and variables:</p>\n\n<pre><code>declare @FinalResults table \nwhile (select COUNT(*) from @PrelimResults) &gt; 0\nbegin\n    select top 1 @CurrID = [UID], @Text = Text from @PrelimResults\n    declare @TextLength int = LEN(@Text )\n    declare @IndexOfDot int = CHARINDEX(\'.\', REVERSE(@Text ), @TextLength - dbo.RegExIndexOf(@Text, \'\\b\' + @FirstSearchWord + \'\\b\') + 1)\n    set @Text = SUBSTRING(@Text, case @IndexOfDot when 0 then 0 else @TextLength - @IndexOfDot + 3 end, 300)\n\n    while (select COUNT(*) from @TempSearchWords) &gt; 0\n    begin\n        select top 1 @CurrWord = Word from @TempSearchWords\n        set @Text = dbo.RegExReplace(@Text, \'\\b\' + @CurrWord + \'\\b\',  \'&lt;b&gt;\' + SUBSTRING(@Text, dbo.RegExIndexOf(@Text, \'\\b\' + @CurrWord + \'\\b\'), LEN(@CurrWord) + 1) + \'&lt;/b&gt;\')\n        delete from @TempSearchWords where Word = @CurrWord\n    end\n\n    insert into @FinalResults\n    select * from @PrelimResults where [UID] = @CurrID\n    delete from @PrelimResults where [UID] = @CurrID\nend\n</code></pre>\n\n<p><strong>Several notes:</strong><br>\n1. Nested while loops probably aren\'t the most efficient way of doing it, however nothing else comes to mind. If I were to use cursors, it would essentially be the same thing?<br>\n2. <code>@FirstSearchWord</code> here to refers to the first instance in the text of one of the original search words, so essentially the text you are replacing is only going to be in the summary. Again, it\'s quite a basic method, some sort of text cluster finding algorithm would probably be handy.<br>\n3. To get RegEx in the first place, you need CLR user-defined functions.</p>\n'}]
['https://Stackoverflow.com/questions/69089', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/1899/']