I have a table with nchar(32) column, with FullText search and auto change tracking activated.
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$%'
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
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?