Matthys Du Toit Matthys Du Toit - 3 months ago 6
SQL Question

Containstable query acting up when searching for values with ' n '

I'm fairly new to full text indexing, my query works as expected, until I search using a phrase containing ' n '.

Example:

SELECT C.name FROM dbo.Clients AS C
INNER JOIN
CONTAINSTABLE (dbo.Clients, name, '"Brick n Clay*"') AS K
ON C.ID = K.[KEY]


This query returns nothing, but a LIKE statement on that same table for that same phrase does.

Does the ' n ' character have any significance for full text searches? Is there a workaround?

Answer

Turns out the significance of the ' N ' was that it (along with many other values) exists in the FullTextIndex STOPLIST which aims to prevent searches on meaningless, frequently used values.

One option is to disable the STOPLIST for your table:

ALTER FULLTEXT INDEX ON MyDB.dbo.MyTable SET STOPLIST = OFF