LIKE condition with comma separated values

Question

This may seem silly but I am working on an old system where we don't have separate table for project. We have a table something like this.

|        PROJECTNAME | EMPID |
|--------------------|-------|
|     casio calc new |     1 |
|           calc new |     2 |
|               tech |     3 |
| financial calc new |     4 |
|              casio |     5 |

Now what we want is to select EmpID from the above table where ProjectName can be either casio or calc. And we have user inputs for multiple selection like casio, calc. Meaning if user inputs casio, calc it should find ProjectName LIKE '%casio%' OR '%calc%'.

I really don't have any idea how can this be possible.

SELECT * FROM ProjectDetails
WHERE ProjectName LIKE 'casio, calc';

I searched for SQL LIKE with IN but I couldn't find any solution. Do anyone have idea how can I achieve this? Or any other approach to use? I am trying this on this fiddle.

Answer 1

Here you go. You can create a dynamic query with the help of REPLACE function with a little bit hack. Then use EXEC function to execute that query.

DECLARE @str VARCHAR(MAX)
SELECT @str='casio,calc'

SELECT @str='LIKE ''%'+REPLACE(@str,',','%'' OR ProjectName LIKE ''%')+'%'''

EXEC('SELECT * FROM ProjectDetails WHERE
     ProjectName ' + @str +'');

Output:

|        PROJECTNAME | EMPID |
|--------------------|-------|
|     casio calc new |     1 |
|           calc new |     2 |
| financial calc new |     4 |
|              casio |     5 |

SQL Fiddle Demo

Thanks to Joe G Joseph for his hint.