CODEWITHSUNDEEP
sql-server-2016
Ksdmg
Ksdmg

Reputation: 449

How to compare a varchar column with multiple values

Is there a way to compare a column with a list of expressions? This is my current code:

WHERE    
   [Description] like 'c:DEC%' 
   or [Description] like 'c:DTI%' 
   or [Description] like 'c:DCT%' 
   or [Description] like 'c:DTB%' 
   or [Description] like 'c:DTX%'

I would like to have something like this:

Where [Description] like ('c:DTI%','c:DCT%','c:DTB%','c:DTX%')

Upvotes: 2

Views: 496

Answers (2)

Squirrel
Squirrel

Reputation: 24763

put it in a temp table

create table #temp
(
    descript varchar(100)
)

insert into #temp (descript) values
('c:DTI%') , ('c:DCT%'), ('c:DTB%'), ('c:DTX%');

SELECT *
FROM   yourtable d 
       INNER JOIN #temp t    ON    d.[Description] LIKE t.descript

Upvotes: 2

Stu
Stu

Reputation: 32579

I would suggest this would be best implemented with a correlation rather than multiple hard-coded conditions, especially if you have hundreds!

You could have a table - either permanent or a table-valued-parameter passed to your procedure, and implement your filtering using exists

from MyTable t
where ...
and exists (select * from LookupTable l where t.[description] like l.MatchText)

Upvotes: 2

Related Questions