I have a table with nchar(32) column, with FullText search and auto change tracking activated.
Data set:
Name, SuperID
Item1, (LO}(bJ$
Item2, (LO}(bJ$
Item3, (LO}(bJ$
I run two queries:
select Name, SuperID from Asset A WHERE A.SuperID LIKE
N'%(LO}(bJ$%'
and
select Name, SuperID from Asset A WHERE ( CONTAINS
(A.SuperID ,N'"(LO}(bJ$"') )
The first one returns 3 rows, and the second returns only 1 row.
Why is it happening? How can I analyse the contents of the Full Text search index? The column values in the first three results are identical. I have tried to update indexes/stats but that doesn't change the outcome.
UPDATE: I have tried removing quotes around the CONTAINS query but I get an error message:
"Syntax error near '(' in the full-text search condition
UPDATE2:
I should have phrased my question in a different way, I think! Have a look at the SuperID column values for the three rows that I would like to return. I have converted them to VARBINARY to ensure that there is no hidden trailing space trickery going on and they are actually the same:
SuperIDasBinary
0x28004C004F007D00280062004A0024002..
0x28004C004F007D00280062004A0024002..
0x28004C004F007D00280062004A0024002..
So why does the CONTAINS query above return only one row instead of three?
"(LO}(bJ$"
, or all do? In the first statement, you're matching values that contain(LO}(bJ$
, in the second"(LO}(bJ$"
. Just want to confirm that the double quotes aren't making a difference.