How to compare a varchar column with multiple values

user_4579959 0
Viewed 103

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%')
sql-server-2016
2 Answers

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

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
user_1856470
Related