Re: Using wildcard criteria in an 'in' statement

On Thu, 9 Mar 2006 10:05:27 -0800, Jig Bhakta wrote:

I need to search for multiple things, all using wildcards but it does not
seem to work:

select *
from Table
where Field1 in ('A%','B%','C%','D%','E%')

Is it possible to do the above or some variation of it without having to
write multile queries or use the OR condition?

Thanks.

Hi Jig,

In this case there is, because you can capitalize on the fact that the
five search strings are all quite similar:

SELECT Col1, Col2, ... -- Never use SELECT * !!!
FROM YourTable
WHERE Col6 LIKE '[ABCDE]%'

Basically, the [ABCDE] says: match on any single character that can be
either A, B, C, D, or E. The % says that anything can follow that first
character.

If your search strings are less similar (e.g. 'Smi%', '%rown' and
'Go[def]ly_d%'), then your only option is to use the OR condition, or
several queries UNION'ed together.

--
Hugo Kornelis, SQL Server MVP
.