How do I search for multiple values in a column where I need to use wildcards?

Question

In SQL Server, I need to search a column for multiple values, but I don't have the exact values, so I need to use wildcards as well.

My current query looks like this:

SELECT * 
FROM table 
WHERE fieldname in ( '%abc1234%',
                     '%cde456%',
                     '%efg8976%')

This doesn't return any results, and yet if I search for any one individual value, I find it, so I know they're in there. Short of doing multiple OR's, which is a bit unwieldy with several hundred values, is there a way to do this?

I'd also be interested to know why this query doesn't work, since the same query without the %'s works just fine (except for the small problem of only catching the few exact matches).

Answer 1

Look at using a Fulltext Index. That should do a much better job with your search, and make your "OR" problem a little nicer to boot:

SELECT * 
FROM table 
WHERE CONTAINS(fieldname, '"abc1234" OR "cde456" OR "efg8976"')

See also:

http://www.simple-talk.com/sql/learn-sql-server/full-text-indexing-workbench/

Answer 2

The reason the query doesn't work is that it looks for an exact match for fieldname within the list of values in the parens. It doen't do a LIKE comparison where the wildcards are taken into account.

So your query is equivalent to:

SELECT *  from table  
where  fieldname  = '%abc1234%' OR
       fieldname  = '%cde456%' OR        
       fieldname  = '%efg8976%' 

Obviously not what you want.