SQL Server, combining LIKE and IN?

Question

Is there an easy way to combine LIKE and IN in one statement in SQL Server, without using a lot of AND and OR?

e.g. I know in MySQL you can do it this way:

SELECT * FROM table1 WHERE column1 REGEXP 'value1|value2|value3'

Answer 1

Not really.

There is no alternation operator in the LIKE pattern syntax. If on 2008 you can use

SELECT *
FROM   table1
WHERE  EXISTS(SELECT *
              FROM   (VALUES ('value1'),
                             ('value2'),
                             ('value3')) Vals(val)
              WHERE  column1 LIKE '%' + val + '%')  

You can also use Regular Expressions in SQL Server but not natively. You need to enable CLR and install an assembly for this.

Answer 2

Another option would be to put the search values in a table and build a dynamic SQL to do the work. It is not recommended but sometimes helps...