2

I have a computed field with a full-text index on it. It's working properly except for just a few records out of thousands, and why remains a mystery:

[fake name]

JAMES J BRATWURST LTDJ LCHM LCDAA1 ACD 1215041803 111.223.3333

A select-query with

... where CONTAINS(searchname,'bratwurst')

works fine, and

... where CONTAINS(searchname,'111.223.3333')

works fine too. But

... where CONTAINS(searchname,'1215041803') does not return anything.

Edit: this wildcard search works too (which shows that the index has been populated):

.. where CONTAINS(searchname, '"121504180*"').

Yet on other similar records, searching the searchname column for a 10-digit "number" using CONTAINS does return values. So it's not that the full-text tokenizer is ignoring numbers.

Thinking that it was possibly a stoplist issue, I turned stoplist off and repopulated the index, but to no avail.

I am open to suggestions for other things to check! Thanks

Tim
  • 8,669
  • 31
  • 105
  • 183
  • I feel like there's something goofy going on here, like a carriage return or something after that value in the original column. Granted I wouldn't think that would matter, but can you test by inserting a duplicate row (with a new PK val of course) or check the leading and trailing unicode with `select unicode(yourColumn) select unicode(reverse(yourColumn))` – S3S Mar 20 '18 at 16:57
  • Goofy indeed. Thanks for the unicode suggestion, but nothing unusual. The string of numbers is using codepoints from the normal ASCII digits range. The 10-digit number is concatenated into the computed column's varchar string as `+ convert(varchar(10),[myColumn])` – Tim Mar 20 '18 at 17:07
  • 1
    I am having this exact issue - really odd – David Masters Jun 27 '23 at 15:04

0 Answers0