SQL Server using wildcard within IN

user_120708 0
Viewed 131561

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?

in-function wildcard sql-server sql
15 Answers

How about:

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

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
GalacticCowboy_296380

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

user_1219710 
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
user_1089770

The IN operator is nothing but a fancy OR of '=' comparisons. In fact it is so '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.

user_1059290

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*"

user_1243670

A bit late to the party, but you could use STRING_SPLIT

SELECT jobdetails.* 
FROM jobdetails
CROSS APPLY  (select value 
              from STRING_SPLIT('540%,%144,orange,coconut',',') 
              WHERE jobdetails.job_no 
              like value) as leek

It wouldn't take very much more work to turn that into a reusable function

user_16522340

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

user_1239510

Try this

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

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

Dan zamir

user_2681360

I'm just learning this stuff but would this work?

select *
from jobdetails
where job_no regexp "^(071)[1|2]";
user_145981520
Related