SQL Server CONTAINSTABLE not working for single digit numbers

Question

This question is about SQL Server's FTS ContainsTable.

To replicate the issue, we can use the script below which will create one table and fill it with addresses.

CREATE TABLE Address (FullAddress nvarchar(100) NOT NULL);  
CREATE UNIQUE CLUSTERED INDEX AddressKey ON Address(FullAddress);  
INSERT INTO Address VALUES ('1 OLD YONGE ST, AURORA, ON');  
INSERT INTO Address VALUES ('1 OLD YONGE ST, NORTH YORK, ON');
INSERT INTO Address VALUES ('1 YONGE ST N UNIT 1, HUNTSVILLE, ON');
INSERT INTO Address VALUES ('1 YONGE ST N UNIT 10, HUNTSVILLE, ON');
INSERT INTO Address VALUES ('18 YONGE ST UNIT 324, TORONTO, ON');
INSERT INTO Address VALUES ('10415 YONGE ST UNIT 1, RICHMOND HILL, ON');
INSERT INTO Address VALUES ('11211 YONGE ST UNIT 37 BUILDING A, RICHMOND HILL, ON');

Now we will create the fulltext catalog and create an index on it.

CREATE FULLTEXT CATALOG AddressCat;  
CREATE FULLTEXT INDEX ON Address(FullAddress) KEY INDEX AddressKey ON AddressCat;

Issue

If we run a query and search for addresses that start with 1 (Notice this is a single digit) and the 1 is NEAR the next term which is Yong, we expect it to return all the first 4 records above. Here is the query:

SELECT * FROM CONTAINSTABLE (Address, FullAddress, '"1" NEAR "Yon*"') ORDER BY RANK DESC;

However, it returns no rows. This is the issue.

But what if we execute a query with double digits such as 11 or 10, then it will return records as expected.

Question:

Why will ContainsTable NOT return any results for single digit searches?

score 9 · Accepted Answer · answered Jan 11 '18 at 17:03

Finding the cause of the issue

I tried many things such as changing the query to the:

SELECT * FROM CONTAINSTABLE (Address, FullAddress, 'NEAR((1, YONGE), 5, TRUE)') 
-- or this
SELECT * FROM CONTAINSTABLE (Address, FullAddress, '1 YON*')

but without any luck.

After some searching online, I started thinking (since the issue only happens with single digits) that it may have something to do with Stopwords:

Stopwords. A stopword can be a word with meaning in a specific language. For example, in the English language, words such as "a," "and," "is," and "the" are left out of the full-text index since they are known to be useless to a search. A stopword can also be a token that does not have linguistic meaning.

Then with the help of this SO Answer, I was able to figure out how SQL Server was interpreting my search. Here is the query and the result of the query:

select * from sys.dm_fts_parser('"1" NEAR "Yon*"',2057, 0, 0)

Notice how the search term 1 is treated as Noise. This was the issue. Then running this query helped me find all the noise words and sure enough the numbers 0-9 were all there:

SELECT ssw.*, ssw.stopword, slg.name
      FROM sys.fulltext_system_stopwords ssw
      JOIN sys.fulltext_languages slg
      ON slg.lcid = ssw.language_id
      WHERE slg.lcid = 1033 -- English

Solution

One solution would be to remove the single digit numbers from the noise words. But I could not find how to do that. Actually, in my case that will not be idea anyhow since the users of my system will only be searching for addresses so if they type is or the, I do not want the system to treat it as noise since they may be searching for a street which starts with is.

I removed the stoplist altogether using the query below and now everything works as expected:

ALTER FULLTEXT INDEX ON [Address] SET STOPLIST = off

Hopefully this helps someone else.

SQL Server CONTAINSTABLE not working for single digit numbers

1 Answers1

Linked