SQL Server using wildcard within IN

Question

Since I believe this should be a basic question I know this question has probably been asked, but I am unable to find it. I'm probably about to earn my Peer Pressure badge, but I'll ask anyway:

Is there a way in SQL Server that I am not aware of for using the wildcard character % when using IN.

I realize that I can use OR's like:

select *
from jobdetails
where job_no like '0711%' or job_no like '0712%'

and in some cases I can use a subquery like:

select *
from jobdetails
where job_no in (select job_no from jobs where job_id = 39)

but I'm looking to do something like the following:

select *
from jobdetails
where job_no in ('0711%', '0712%')

In this case it uses the percent sign as a character instead of a wildcard character so no rows are returned. I currently just use a bunch of OR's when I have to do this, but I know there has to be a better way. What method do you use for this?

Answer 1

How about:

WHERE LEFT(job_no, 4) IN ('0711', '0712', ...)

Answer 2

You could try something like this:

select *
from jobdetails
where job_no like '071[12]%'

Not exactly what you're asking, but it has the same effect, and is flexible in other ways too :)

Answer 3

SELECT c.* FROM(
SELECT '071235' AS token UNION ALL SELECT '07113' 
 UNION ALL SELECT '071343'
UNION ALL SELECT '0713SA'
UNION ALL SELECT '071443') AS c
JOIN (
SELECT '0712%' AS pattern UNION ALL SELECT '0711%' 
 UNION ALL SELECT '071343') AS d
ON c.token LIKE d.pattern

071235
07113
071343

Answer 4

Peer Pressure is for removing an asnwer, does it apply to questions too? I worked soo hard to earn mine ;). Ans was actually a valid response lol.

Anyway, the IN operator is nothing but a fancy OR of '=' comparisons. In fact is soo 'nothing but' that in SQL 2000 there was a stack overflow bug due to expansion of the IN into ORs when the list contained about 10k entries (yes, there are people writing 10k IN entries...). So you can't use any wildcard matching in it.

Answer 5

You have the answer right there in your question. You cannot directly pass wildcard when using IN. However, you can use a sub-query.

Try this:

select *
from jobdetails
where job_no in (
select job_no
from jobdetails
where job_no like '0711%' or job_no like '0712%')
)

I know that this looks crazy, as you can just stick to using OR in your WHERE clause. why the subquery? How ever, the subquery approach will be useful when you have to match details from a different source.

Raj

Answer 6

How about something like this?

declare @search table
(
    searchString varchar(10)
)

-- add whatever criteria you want...
insert into @search select '0711%' union select '0712%'

select j.*
from jobdetails j
    join @search s on j.job_no like s.searchString

Answer 7

In Access SQL, I would use this. I'd imagine that SQLserver has the same syntax.

select * from jobdetails where job_no like "0711*" or job_no like "0712*"

Answer 8

Try this

select * from jobdetails where job_no between '0711' and '0713'

the only problem is that job '0713' is going to be return as well so can use '07299999999999' or just add "and job_no <> '0713'

Dan zamir