SQL FullText Search and highlight result

Question

I have a table in SQL Server with structure:

 Students
(

  StudentId bigint,

  FullName nvarchar(100),

  DegreeId smallint, 

  Articel nvarchar(max)
)

i have created a full text index and enabled it for "students" table.

in my asp.net page ,users type words and i call a stored proc filter students using these words.

   Create Procedure GetStudents(@Article nvarchar(200)=typed words)

   AS
   BEGIN
         SET NOCOUNT ON
         SELECT StudentId,FullName,DegreeId,Article
         FROM Students 
         WHERE WHERE FREETEXT(Article,'''+ @Article+''')
   END

The search works fine ,now i need to highlight(yellow background)the "articles" of returned stuents in my asp.net page(by jquery,or asp.net).

Any suggestions.

Thanks StackOverFlow

score 0 · Answer 1 · edited May 23 '17 at 11:47

0

See here Highlight a word with jQuery

edited May 23 '17 at 11:47

Community

1
1

answered Mar 29 '11 at 22:23

Daveo

19,018
10
48
71

score 0 · Answer 2 · answered Mar 29 '11 at 22:27

I think you're going to have a hard time getting a perfect highlighting system since SQL Server will match word variations in a FREETEXT query. If you wanted to be nearly perfect, you could do your own (or use someone's) stemming algorithm to generate word variations.

If you wanted to do a best-efforts, you could embed the query (in a hidden input field or as a hash on the querystring), then do a javascript search-and-replace like this:

$(document).ready(function() {
    // Set up words, either as a list you loop through or a JSON collection
    $("#resultsContainer").innerHTML.replace(word, "<span class='highlight'>" + word + "</span>");
});

You could probably just as easily do this server-side and not have to worry about jQuery picking up elements of your page that might match the query words. In that case, you would simply do a search-and-replace similar to the javascript, only on your server code (VB or C#).

thank you.Actually i'm thinking to search just with like '%word%' but i don't know how to do it i have a list of words — , Mar 29 '11 at 22:52
If you have a list of words, you're going to have to `OR` a lot of `LIKE` evaluations, which is probably going to make for an extremely slow query. You're on the right track with a full-text search in terms of performance. Here is a C# implementation of the Porter stemming algorithm: http://tartarus.org/~martin/PorterStemmer/csharp2.txt. You could use that to generate the words to highlight based on the simple query words. — Josh Anderson, Mar 30 '11 at 11:12

score 0 · Answer 3 · answered Mar 30 '11 at 07:19

0

Try this this might help you

highlight words

answered Mar 30 '11 at 07:19

Harish

2,311
4
23
28

SQL FullText Search and highlight result

3 Answers3