SQL Server CONTAINS with digits gives no results

Question

I have a database table which is full-text indexed and i use the CONTAINS-function to perform a search-query on it.

When I do:

SELECT * FROM Plants WHERE CONTAINS(Plants.Description, '"Plant*" AND "one*"');

I get back all correct results matching a description with the words "Plant" and "one".

Some plant are named like "Plant 1", "Plant 2" etc. and this is the problem.

When i do this, i get no results:

SELECT * FROM Plants WHERE CONTAINS(Plants.Description, '"Plant*" AND "1*"');

Anyone know why?

score 2 · Accepted Answer · answered Mar 25 '10 at 13:31

2

There is a list of commonly-used words that are not indexed in a keyword search, such as "and" and "the".

I believe the text "1" also appears in that list. Therefore it doesn't appear in the index, and can't be found with the CONTAINS clause.

If I recall correctly, there is an admin interface to allow you to edit that list of common words. I tried editing it once, a few years ago, and I recall having trouble telling the difference after I did.

answered Mar 25 '10 at 13:31

Oddthinking

24,359
19
83
121

1

Thanks, that was it. The files were located here: Program Files\Microsoft SQL Server\MSSQL.x\MSSQL\FTData\noiseXXX.txt – dale Mar 25 '10 at 13:44

score 0 · Answer 2 · answered Mar 25 '10 at 13:24

0

Daan is correct. you need another * before the 1. Placing wildcards either side of a search term searches the entire string for the search term regardless of its position.

answered Mar 25 '10 at 13:24

codingbadger

42,678
13
95
110

Nope, thats not it, same results with or without stars in the beginning – dale Mar 25 '10 at 13:25

SQL Server CONTAINS with digits gives no results

2 Answers2