Search code examples
sqlsql-servert-sql

Get records whose values in a certain column contain any of a list of strings

I have a table in SQL Server which has several columns, one of which is "Payload", I want to select all records with payloads that contain any one of a (long) list of values.

Something like

select *
from myTable
where Payload contains ('%value1%', '%value2%', ...)

The list of values can be quite long so having "where Payload like '%value1%' or Payload like '%value2%' ... " isn't optimal. I also cannot alter the table in any way and it does not have an index.

What options, besides a cursor do I have?

Solution

  • You can do it with temp table as below:

    insert the values into a temp table. 

create table temp (val varchar(15)) 

insert into temp values
('value1' ), ('value2' )

select *
from myTable
where Payload 
join temp on Payload like concat('%', val, '%')