Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

1 Answer 1

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;
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.