CODEWITHSUNDEEP
sql-serverselect
Kirsten
Kirsten

Reputation: 131

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

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).

Upvotes: 6

Views: 81574

Answers (3)

Judge Mental
Judge Mental

Reputation: 5239

select table.* from (
    table join
    ( select * from values (
        ( '%abc1234%' ), ( '%cde456%' ), ( '%efg8976%' )
    ) ) as search( exp ) on 0 = 0
) where fieldname like exp

or perhaps

select table.* from
    table join
    ( select * from values (
        ( '%abc1234%' ), ( '%cde456%' ), ( '%efg8976%' )
    ) ) as search( exp )
 on fieldname like exp

modulo some syntax I'm sure.

The point being that this comes close to allowing the list of values to be the only parameter.

Upvotes: 3

Andrew Cooper
Andrew Cooper

Reputation: 32576

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.

Upvotes: 4

Joel Coehoorn
Joel Coehoorn

Reputation: 415800

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/

Upvotes: 17

Related Questions