How to handle single character search terms in MS-SQL FreeText searching?

Question

I am having a problem with a FreeText search, currently running on a SQL 2000 server.

In a table of approximately 1.3 million rows which contain company names, I am attempting to use a FreeText query. However since SQL Server strips out special characters and single characters when building its index, our code does the same when submitting the query.

For example searches like 'Texas A & M' end up only querying for 'Texas' which returns a ton of irrelevant records.

What's the best-practice for handling these sorts of search queries? Would this problem be rectified by upgrading to a newer version of SQL Server?

At this point a third-party indexing engine like Lucene is not an option, even if it would fix the problem, which I am not sure of.

Answer 1

You may check if improvements of in SQL Server 2005 can solve your problem: SQL Server 2005 Full-Text Search: Internals and Enhancements, in particular about Noise Words in New Features for the Developer.

Answer 2

if you are searching company names and not long passages of text, why not just use LIKE?

...
WHERE
    CompanyName LIKE '%Texas A%&%M%'

Answer 3

You can try using a single character wildcard '_' similar to:

WHERE myColumn like 'Texas_A_&_M'

or

WHERE myColumn like 'Texas%A_&_M'