CODEWITHSUNDEEP
sqlsql-serverwildcardin-function
Dusty
Dusty

Reputation: 4707

SQL Server using wildcard within IN

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?

Upvotes: 56

Views: 165050

Answers (16)

Willie
Willie

Reputation: 1

I had this same question and found a very simple solution using the SUBSTRING function in the WHERE clause. This will not affect how the job_no displays in the return, rather it just trims the IN search down based on the criteria entered for the substring function.

The advantage of using SUBSTRING versus LEFT or RIGHT is that you can define the starting character so if you just want to search the middle of a string, you can do so.

SELECT *
FROM jobdetails
WHERE SUBSTRING(job_no, 1, 4) IN ('0711', '0712')

Upvotes: 0

ShrapNull
ShrapNull

Reputation: 1350

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

Upvotes: 0

Wyatt Nelson
Wyatt Nelson

Reputation: 1

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

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

Upvotes: -1

Remus Rusanu
Remus Rusanu

Reputation: 294407

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.

Upvotes: 1

Tim
Tim

Reputation: 41

  1. I firstly added one off static table with ALL possibilities of my wildcard results (this company has a 4 character nvarchar code as their localities and they wildcard their locals) i.e. they may have 456? which would give them 456[1] to 456[Z] i.e 0-9 & a-z

  2. I had to write a script to pull the current user (declare them) and pull the masks for the declared user.

  3. Create some temporary tables just basic ones to rank the row numbers for this current user

  4. loop through each result (YOUR Or this Or that etc...)

  5. Insert into the test Table.

Here is the script I used:

Drop Table #UserMasks 
Drop Table #TESTUserMasks 

Create Table #TESTUserMasks (
    [User] [Int] NOT NULL,
    [Mask] [Nvarchar](10) NOT NULL)

Create Table #UserMasks (
    [RN] [Int] NOT NULL,
    [Mask] [Nvarchar](10) NOT NULL)

DECLARE @User INT
SET @User = 74054

Insert Into #UserMasks 
select ROW_NUMBER() OVER ( PARTITION BY ProntoUserID ORDER BY Id DESC) AS RN,
       REPLACE(mask,'?','') Mask
from dbo.Access_Masks 
where prontouserid = @User

DECLARE @TopFlag INT
SET @TopFlag = 1

WHILE (@TopFlag <=(select COUNT(*) from #UserMasks))
BEGIN
    Insert Into #TestUserMasks 
    select (@User),Code from dbo.MaskArrayLookupTable 
    where code like (select Mask + '%' from #UserMasks Where RN = @TopFlag)

    SET @TopFlag = @TopFlag + 1
END
GO

select * from #TESTUserMasks

Upvotes: 3

notYetExpert
notYetExpert

Reputation: 171

I think I have a solution to what the originator of this inquiry wanted in simple form. It works for me and actually it is the reason I came on here to begin with. I believe just using parentheses around the column like '%text%' in combination with ORs will do it.

select * from tableName
where (sameColumnName like '%findThis%' or sameColumnName like '%andThis%' or 
sameColumnName like '%thisToo%' or sameColumnName like '%andOneMore%')

Upvotes: 17

Dan Mihalea
Dan Mihalea

Reputation: 184

As Jeremy Smith posted it, i'll recap, since I couldn't answer to that particular question of his.

select *
from jobdetails
where job_no like '071[1-2]%'

If you just need 0711% and 0712% you can also place a ranges within the brackets. For the NOT keyword you could also use [^1-2]%

Upvotes: 1

Pablo
Pablo

Reputation: 1

This might me the most simple solution use like any

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

In Teradata this works fine.

Upvotes: -4

Julian
Julian

Reputation: 41

I had a similar goal - and came to this solution:

select *
from jobdetails as JD
where not exists ( select code from table_of_codes as TC 
                      where JD.job_no like TC.code )

I'm assuming that your various codes ('0711%', '0712%', etc), including the %, are stored in a table, which I'm calling *table_of_codes*, with field code.

If the % is not stored in the table of codes, just concatenate the '%'. For example:

select *
from jobdetails as JD
where not exists ( select code from table_of_codes as TC 
                      where JD.job_no like concat(TC.code, '%') )

The concat() function may vary depending on the particular database, as far as I know.

I hope that it helps. I adapted it from:

http://us.generation-nt.com/answer/subquery-wildcards-help-199505721.html

Upvotes: 4

Dan Zamir
Dan Zamir

Reputation: 11

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

Upvotes: -1

PowerUser
PowerUser

Reputation: 11791

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

Upvotes: 0

A-K
A-K

Reputation: 17090

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

Upvotes: 4

Raj
Raj

Reputation: 10853

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

Upvotes: -1

GalacticCowboy
GalacticCowboy

Reputation: 11759

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

Upvotes: 11

Jeremy Smyth
Jeremy Smyth

Reputation: 23503

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

Upvotes: 8

Aaron Alton
Aaron Alton

Reputation: 23236

How about:

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

Upvotes: 32

Related Questions