Best way to search in a varchar column in sql server

Question

What would you recommend to search a sql server table (varchar(max) column) for a term?

Let's say, like in ebay, if you search for "wii brand new", you get results like "Brand New Nintendo Wii Fit Game + Balance Board Bundle", "Wii Fit (Wii) BRAND NEW WII FIT GAME + BALANCE BOARD".

I think it basically searches every word and returns the ones that contains all the words, what would you recommend?

Answer 1

You are looking for fulltext indexing, it allows you to do more advanced querying than regular expressions or like.

Check this article for a quick introduction, the instructions are for SQL Server 2000, where it is a little harder to setup than in 2005 or 2008.

Relevant quote:

 With full-text searching, you can perform many other types of search:

 * Two words near each other
 * Any word derived from a particular root (for example run, ran, or running)
 * Multiple words with distinct weightings
 * A word or phrase close to the search word or phrase

Answer 2

Depends on what you are trying to do. For a simple search, you could just do select * from table where field like '%word%'. But if this is some sort of application feature, you want to look into a full tet search application. It can store words that appear in that field as indexes and then search accross those words instead of using that field.