Prefix full text search with numbers in SQL Server 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.
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;