SQL-Server Full Text Index Unexpected results

DELETE FROM TestFullTextSearch insert into TestFullTextSearch values (1, '235_ldzr_2 testing ') insert into TestFullTextSearch values (2, 'test 235_ldzr_2 testing ') insert into TestFullTextSearch values (3, 'A 235_ldzr_2 testing ') insert into TestFullTextSearch values (4, '1 235_ldzr_2 testing ') insert into TestFullTextSearch values (5, '12 235_ldzr_2 testing ') insert into TestFullTextSearch values (6, '123 235_ldzr_2 testing ') SELECT * from TestFullTextSearch where contains(AllText, '"235*"') SELECT * from TestFullTextSearch where contains(AllText, '"235_*"')

SQL Server has a list of words and symbols which are considered noise, these are excluded from the index, and you can't search for it specifically. You can edit this list, or you can create a new one. See here for the how-to.

Apparently, you can even edit it at file level, see here.

Edit:

I've been able to reproduce the problem:

create table TestFullTextSearch (
Id int not null,
AllText nvarchar(400)
)

create unique index test_tfts on TestFullTextSearch(Id);
create fulltext catalog ftcat_tfts;
create fulltext index on TestFullTextSearch(AllText)
key index test_tfts on ftcat_tfts
with change_tracking auto, stoplist off
go

insert into TestFullTextSearch values (1, 'legacyreport Report Legacy 23049823490  20150713 Cardiac US ')
insert into TestFullTextSearch values (2, '123-45-678 foo bar  19450712 20020723 Exercise Stress US ')
insert into TestFullTextSearch values (3, '2048 jj goodguy xy2000 19490328 20150721 Cardiac US ')
insert into TestFullTextSearch values (4, '12345678 4.0 ALLCALCS  19650409 20031103 Cardiac Difficult US ')
insert into TestFullTextSearch values (5, 'Veterinary Products Beaphar Fiprotec Spot On Small Dog 67mg x 1 235_ldzr_2 Beaphar 87112311437_ldzr_2 ')

On this table, if I execute

select * 
from   TestFullTextSearch 
where  contains(AllText, '"235_ldzr_2*"')

I get no results. However, if I add a backslash:

select  * 
FROM    TestFullTextSearch 
WHERE   CONTAINS(AllText, '"235\_ldzr_2*"')

I do get a result!

I don't understand though. If I add another row:

insert into TestFullTextSearch values (6, 'Veterinary Products Beaphar Fiprotec Spot On Small Dog 67_mg x 1 235_ldzr_2 Beaphar 87112311437_ldzr_2 ')

And I search for WHERE CONTAINS(AllText, '"67_*"'), I get the result back as expected... Other tests as well, don't show the same behaviour as contains(AllText, '"235_ldzr_2*"').

By the way, my LCID = 1033.

One more test case:

Insert into TestFullTextSearch values (15, 'Veterinary Products Beaphar Fiprotec Spot On Small Dog 100_ldzr_2 x 1 225_ldzr_2 Beaphar 87112311437_ldzr_2 ')

This record I can find with

CONTAINS(AllText, '"100_*"')

However, this record, where I added x 1 in front of the 100_ldzr:

Insert into TestFullTextSearch values (16, 'Veterinary Products Beaphar Fiprotec Spot On Small Dog x 1 100_ldzr_2 x 1 225_ldzr_2 Beaphar 87112311437_ldzr_2 ')

I cannot find with the same CONTAINS, but I can find it if I add a backslash.

Conclusion (for now)

It seems there can be an issue when numbers are followed by a space, then followed by numbers ending in an underscore. Consider the following:

select * from sys.dm_fts_parser('"x 235_*"', 1033, 0, 0)

This results in 2 search terms: "x" and "235_"

This however:

select * from sys.dm_fts_parser('"1 235_*"', 1033, 0, 0)

Results in seven search terms: "1 235", "1235", "1", "235", and "_". (1 and 235 also occur as nn1/nn235).

The absence of "235_" explains why it could not be found. When I added a backslash, it was interpreted as word divider , matching 235\_ to 235 (and to _).

I'm pretty confident this is the cause. The solution is probably more difficult.

Recommended topics

Hot tags