Full Text Search: Search for Sound Words

Question

I have a database in SQL Server 2008 with full-text indexes. I defined "Stop" in the "Stop List". However, when I search for any phrase with the keyword "al", the word "al" still remains in the ranking.

This may be due to the fact that I am breaking search queries and reconstructing them. Then I look through several fields and evaluate the results: http://pastebin.com/fdce11ff . This allows you to split the search.

'al hamra' 

in

 ("*al*" ~ "*hamra*") OR ("*al*" OR "*hamra*") 

for full text search.

Imagine this scenario:

Name: Al Hamra, Author: Jack Brown, Genre: Fiction Al Karawan, Author: Al Hanz, Genre: Romance

Now searching for 'al hamra' will return 'Al Karawan' , despite the fact that 'al' is in the stop list. Why is this? I thought stoplists would make words lose their weight?

Answer 1

Noisy words are specific to code pages; did you add it to the correct one? You can use sys.dm_fts_parser to test it (see below), it can also work better than your manual word that violates the code (or not).

 SELECT special_term, display_term FROM sys.dm_fts_parser (' "al hamra" ', 1033, 0, 0) 

Assuming you are using code page 1033. If your noise word is on the code page you expect, it should be visible as a character in the list.