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!
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With