How to do a broad-match search with TSQL FreeText search with numeric values?

I am trying to do a free text search on terms like "sony model number np-fx110" or "carrying case for sony sz-vgn770N" or "asus laptop model g60v".

I know that freetext treats numbers like noise, so I figured I would edit out numbers from the noise SQL Server 2008 noiseENU.txt file but I'm unable to locate any noise files on my server.

The FTData directory only contains a bunch of 2kb xml files and an empty FilterData folder.

I've got a split Function that will return all words in a given string with a split character of a space. I need a CTE that will concatenate the AND text for each word in returned from the SPLIT() function.

> SELECT *  FROM MyTable WHERE
> Description LIKE '%asus%'  AND
> Description LIKE '%laptop%'  AND
> Description LIKE '%model%'  AND
> Description LIKE '%g60v%'

Essentially the model number can be anywhere in the description field. I can easily do a LIKE [model number], however I would want to match that up with other words contained in the description.

This doesn't seem like an optimal approach, but workable. Any other suggestions on how to tackle this would be appreciated.

Thanks.

UPDATE

Here is the result of running "exec sp_help_fulltext_catalogs"

> ftcatid:5 NAME: CTIMytemDataCatalog   
> PATH: NULL                            
> STATUS: 0 
> NUMBER_FULLTEXT_TAB开发者_如何学CLES: 1

UPDATE

A problem arises when the model number is simply a number, such as "4" in "iphone 4".

I am already dynamically building a CONTAINS query, inserting AND in between each term. However, here are some issues:

> **WHERE CONTAINS(*, 'iphone AND 4 AND battery')** - returns 0 results
> **WHERE FREETEXT(*, 'iphone 4 battery')** - is matching for iphone
> OR battery which is sub-optimal
> **WHERE CONTAINS(*, '"iphone 4" AND "battery"')** - returns the most
> accurate result set

Given these findings I'm thinking that I should maintain a list of these "special cases" in a table, and generate my CONTAINS query dynamically based on this.

Any suggestions about this approach?

Have you tried using a full text index?

You are correct that SQL treats "1" or "2" as noise by default (you can change this)... but it should find "g60v" or "sz-vgn770N" no problem. I have used FTS in similar fashion searching a catalog of books by ISBN and it worked fine.

Lastly, I may be wrong on this one... but I don't think the noise file gets created until you actually build/use FTS.

Also note that searching FTS isn't similar to using a like statement. Read up on the different methods for searching FTS with Books Online if you're not familiar with the syntax.

A query like this should work for your needs: (how I use FTS with my book catalog search)

WHERE FREETEXT (*, 'g60v asus laptop model' );

Read more about freetext queries here: http://msdn.microsoft.com/en-us/library/ms176078.aspx