CODEWITHSUNDEEP
sqlt-sqlsql-server-2005full-text-searchfull-text-indexing
Umair
Umair

Reputation: 3243

Strange issue with SQL contains - ignoring starting characters of a string

I am experiencing a strange issue with the sql full text indexing. Basically i am searching a column which is used to house email addresses. Seems to be working as expected for all cases i tested except one!

SELECT *
FROM Table
WHERE CONTAINS(Email, '"[email protected]"')

For a certain email address it is completely ignoring the "email" part above and is instead doing 

SELECT *
FROM Table
WHERE CONTAINS(Email, '@me.com')

There was only one case that i could find that this was happening for. I repopulated the index, but no joy. Also rebuilt the catalog.

Any ideas??

Edit: I cannot put someone's email address on a public website, so I will give more appropriate examples. The one that is causing the issue is of the form:

[email protected]

When i do

WHERE CONTAINS(Email, "'[email protected]"')

The matching rows which are returned are all of the form .*@somedomain.net.au. I.e. it is ignoring the a.b.c part.

Upvotes: 1

Views: 1726

Answers (1)

Suroh
Suroh

Reputation: 91

Full stops are treated as noise words (or stopwords) in a fulltext index, you can find a list of the excluded characters by checking the system stopwords:

SELECT * FROM sys.fulltext_system_stopwords WHERE language_id = 2057 --this is the lang Id for British English (change accordingly)

So your email address which is "[email protected]" is actually treated as "a b [email protected]" and in this particular case as individual letters are also excluded from the index you end up searching on "@somedomain.net.au"

You really have two choices, you can either replace the character you want to include before indexing (so replace the special characters with a match tag) or you remove the words/character you which to include from the Full Text Stoplist.

NT// If you choose the latter I would be careful as this can bloat your index significantly.

Here are some links that should help you :

Configure and Manage Stopwords and Stoplists for Full-Text Search

Create Full Text Stoplists

Upvotes: 2

Related Questions