SQL Server CONTAINS with digits gives no results

Question

Hi,

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?

Answer 1

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.

Answer 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.