SQL Server oddity with full-text indexing and case sensitivity and numbers
1
vote
0
answers
145
views
This problem may be unique to our server, but I can't tell from the symptoms where the issue may lie.
My SQL Server version is Microsoft SQL Server 2019 (RTM) - 15.0.2000.5 (X64) Sep 24 2019 13:48:23 Copyright (C) 2019 Microsoft Corporation Enterprise Edition (64-bit) on Windows Server 2019 Standard 10.0 (Build 17763: ) (Hypervisor)
The Server Level Collation: SQL_Latin1_General_CP1_CI_AS;
The Database Maintenance Collation: SQL_Latin1_General_CP1_CI_AS
I have a field (searchtitle) in a table (fsItems) that has a full-text index turned ON. For the record with primary key (IDItem) 704629, the content of this full-text indexed field is "TEST AFA 0 TEST".
Surprisingly, the following query returns no results:
SELECT * FROM fsItems WHERE CONTAINS(searchtitle,'AFA') AND IDItem = 704629
However, if I change the content to be "TEST afa 0 TEST" or "TEST AFA O TEST" (capital "O" instead of zero) the query returns the record. (It also returns the record if I change the content to "TEST AFB 0 TEST" and change the CONTAINS argument to AFB.)
At first I thought maybe AFA was some kind of stop word (I checked, it isn't), but that wouldn't explain why changing zero to upper-case "O" returns the proper result.
Any idea what is going on here? In other words, the query doesn't seem to be able to find "AFA" unless it is "afa" or unless I make some seemingly unimportant changes to the text itself, like change a number to a letter, or change the spelling of AFA to AFB. Why would that be?
Thanks for any suggestions
Asked by user1693404
(11 rep)
Jan 25, 2022, 05:43 PM
Last activity: Jan 26, 2022, 03:35 AM
Last activity: Jan 26, 2022, 03:35 AM