Sql-server – Prefix full text search with numbers in SQL Server

full-text-searchsql serversql-server-2008-r2

If I create the following table in SQL Server 2008 R2:

CREATE TABLE test(
  id BIGINT IDENTITY NOT NULL,
  value VARCHAR(10),
  CONSTRAINT test_pk PRIMARY KEY (id)
);

CREATE FULLTEXT CATALOG test_catalog;

CREATE FULLTEXT INDEX ON test
  (
    value Language 0
  )
  KEY INDEX test_pk
  ON test_catalog;

ALTER FULLTEXT INDEX ON test START UPDATE POPULATION;

INSERT INTO test (value) VALUES ('Xstreet 7');
INSERT INTO test (value) VALUES ('Ystreet 8');
INSERT INTO test (value) VALUES ('Zstreet');
INSERT INTO test (value) VALUES ('Wstreet9');

Wait until the index is populated and perform a prefix search for 'Ystreet 8':

SELECT * from test WHERE CONTAINS(*, '"Ystreet 8*"');

I get no results. Why is that?

Searches for '"Ystreet 8"', '"Ystreet*"' and "'Wstreet9*"' works as expected.

Best Answer

It seems the problem is caused by 8 (and 0,1,2,3,4,5,6,7 and 9) being on the stopword list.

A workaround is to base the table on an empty stopword list (or at least one without the 1-digit numbers):

CREATE FULLTEXT STOPLIST empty_stoplist;
ALTER FULLTEXT INDEX ON test SET STOPLIST empty_stoplist;
ALTER FULLTEXT INDEX ON test START UPDATE POPULATION;
Related Question