Full text search CONTAINSTABLE

Question

I have table with many fields. For searching information in this table I use full text search. When I try to find rows, where in phone number or in Skype name fields exist three zeros, I, also with correct records, get records which contains some string with point and numbers (for ex. string.87)

I use query below

DECLARE @SearchCondition nvarchar(100) = '"000*"'

SELECT e.Id, e.FirstName, e.LastName, e.PhoneNumber, e.SkypeName
FROM dbo.Employee e
    INNER JOIN CONTAINSTABLE([dbo].[Employee], *, @SearchCondition, Language 0) as fti ON e.Id = fti.[KEY]

And I getting the following results:

Id  FirstName   LastName     PhoneNumber    SkypeName
14  name1       name1      3903749282      000skypename
20  name90      name.90    3906773464      skypename_str
21  name2       name2      3906769539      skypename.87

Is there a way to fix it? Thanks!

Solution 1

You can see what your search term looks like after the word breaker is done with it thus:

SELECT display_term FROM sys.dm_fts_parser('"000*"', 0, NULL, 0);

Gets:

000
nn0

(See http://msdn.microsoft.com/en-us/library/cc280463.aspx for the docs.)

The second term 0 is the language you are specifying for the word breaker. You are specifying the neutral word breaker which appears to round off multiple zeros.

Changing it to English (1033):

SELECT display_term FROM sys.dm_fts_parser('"000*"', 1033, NULL, 0);

...makes the query do what you want, but then you might have other reasons for using Language 0. If you don't specify a language in the CONTAINSTABLE, it uses the language the column was indexed in.

Does

CONTAINSTABLE([dbo].[Employee], *, @SearchCondition)

..work for you?

EDIT

After a little fooling around, it looks like the neutral word breaker is trying to also find numeric arguments in scientific notation:

SELECT * FROM sys.dm_fts_parser('850000000000000', 0, NULL, 0);

850000000000000
nn8d5e+014

Solution 2

If you don't want to match on data from columns besides PhoneNumber and SkypeName change the columns from '*' to those columns you want.

CONTAINSTABLE([dbo].[Employee], (PhoneNumber, SkypeName), @SearchCondition, Language 0)

Also, if it isn't already on, look into background updating of your indexes.