How to do CONTAINS () in two columns of a full-text SQL search index

Question

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

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

Sample data:

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

and I made a full text index in the table after creating the directory using the default parameters.

How can I execute the following query:

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

But my query does not return any results, which does not make sense, because it works:

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

so:

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

but this also does not work:

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

Why can't I execute the query in the indexed column of the primary key (Name) when I selected this column to be included in the Address column when setting up the full-text index?

Thanks in advance!

Answer 1

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

The rationale for CONTAINS (NameID, '1'), which does not return any search results, is that '1' (and other such small numbers) are considered full-text noise words and are filtered out during indexing.

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

 select * from sys.fulltext_system_stopwords where language_id = 1033; 

You need to disable or change the list of stops, an example of which can be found here .

Answer 2

I think the biggest problem here (and I edited my question to reflect this) is that I have integers representing the name of the primary key, the Contains () function in the full text directory is incompatible. This is unfortunate, and I'm still looking for a complete textual alternative to working with integer directories.