CODEWITHSUNDEEP
sql-serverfull-text-search
D Deshmane
D Deshmane

Reputation: 1245

MS SQL : Full Text Search Results are not relevant

I am trying MS SQL Full Text Query on single column.

For this I am using "FREETEXTABLE" function.

When I query "Horse ride" the result set contains videos where title contain the word "ride".

No wonder that when using FREE or "FREETEXTTABLE" the process is to break query string
into words, create inflectional words and that is how the result set get generated.

So my question is if this is the process, why the result set have no video where the "horse" word is
present (I have videos in DB where videos title contains the "horse" word).

Is it because the word breaker gives preference to "verbs" ?

Please comment on how "word breaker" and "stemmer" works for English language.

Links where I could find grate details about "word breaker" and "stemmer" will also be help full.

This is very important for me to get relevant results every time.

Thank you.

Upvotes: 1

Views: 1063

Answers (1)

M.Ali
M.Ali

Reputation: 69494

Full text search filters the noise words and punctuations and you have the flexibility of adding more noise words to the default list of noise words. But to manipulate verbs, inflectional or synonyms we can make use of different functions in where clause.
In your case if you are looking for fields where the word "Horse" AND "ride" exists you can simply make use of Contains function, something like this....

SELECT ColumnName
FROM TableName
WHERE Contains(ColumnName, '"horse" AND "ride"')

If you are looking for values where there is word "Horse" and any inflectional form of "ride" say like ride, riding. You can use something like this ....

SELECT ColumnName
FROM TableName
WHERE Contains(ColumnName, '"horse"') AND CONTAINS(ColumnName, 'FORMSOF(INFLECTIONAL, ride)')

Upvotes: 1

Related Questions