How to do a CONTAINS() on two columns of Full Text

Question

I have a table (MyTable) with the following columns:

Col1: NameID VARCHAR(50) PRIMARY KEY NOT NULL Col2: Address VARCHAR(255)

Data Example:

Name: '1 24' Address: '1234 Main St.'

and i did a full text index on the table after making the catalog using default params.

How can I achieve the following query:

 SELECT * FROM MyTable
 WHERE CONTAINS(NameID, '1')
 AND CONTAINS(Address, 'Main St.');

But my query is returning no results, which doesn't make sense because this does work:

 SELECT * FROM MyTable
WHERE CONTAINS(Address, 'Main St.');

and so does this:

 SELECT * FROM MyTable
 WHERE CONTAINS(Address, 'Main St.')
 AND NameID LIKE '1%'

but this also doesn't work:

 SELECT * FROM MyTable
 WHERE CONTAINS(NameID, '1');

Why can't I query on the indexed, primary key column (Name) when I selected this column to be included with the Address column when setting up the Full Text Index?

Thanks in advance!

Answer 1

I think the biggest problem here (and I edited my question to reflect this) is that I've got integers representing the primary key's name, which Contains() function on the full text catalog is not compatible. This is unfortunate and I'm still searching for a full text alternative to working with catalogs of integers.

Answer 2

Since the NameID field is of type varchar, full-text will handle the indexing just fine.

The reasoning behind CONTAINS(NameID, '1') not returning any search results is that '1' (and other such small numbers) are regarded as noise words by full-text and filtered out during indexing time.

To get a list of the stop words, run the following query -

select * from sys.fulltext_system_stopwords where language_id = 1033;

You need to turn off or modify the stop list, an example of which can be found here.