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.

possible duplicate of [Using SQL LIKE and IN together](http://stackoverflow.com/questions/2318126/using-sql-like-and-in-together) — Tienou, Jul 11 '14 at 13:34

score 1 · Answer 1 · answered Jul 11 '14 at 13:22

1

The solution is to use several LIKE conditions:

SELECT *
FROM ProjectDetails
WHERE ProjectName LIKE '%casio%'
OR ProjectName LIKE '%calc%';

answered Jul 11 '14 at 13:22

Aaron Digulla

321,842
108
597
820

1

I would add just a hint, Always put or conditions inside parenteshis :) – Jorge Campos Jul 11 '14 at 13:24
Hello, Isn't there any dynamic solution? – user3829465 Jul 11 '14 at 13:24
@user3829465: SQL isn't a dynamic language, so your question doesn't make sense. Aren't you building the query in a real programming language? If not, then you need to find out how you can build queries at runtime in your database. I don't know SQL Server to help you there. – Aaron Digulla Jul 11 '14 at 13:30
Or parse the delimited list of values. – Sean Lange Jul 11 '14 at 14:02

score 1 · Answer 2 · answered Jul 11 '14 at 13:24

1

Like doesn't except comma delimited values. You are going to have to dynamically add an or condition for each passed in value.

It has to be of the form:

ProjectName Like'%param1%' OR ProjectName Like '%param2%'

answered Jul 11 '14 at 13:24

Vulcronos

3,428
3
16
24

score 1 · Accepted Answer · edited May 23 '17 at 10:32

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.

score 0 · Answer 4 · answered Oct 18 '17 at 10:32

We declare @str variable and store value we want to search and using REPLACE function place like keyword for every value and assign it to main query and execute it, you can get result

DECLARE @Query VARCHAR(MAX)
DECLARE @str VARCHAR(MAX)
SELECT @str='tech,casio, calc,casio calc new'
SELECT @str='LIKE ''%'+REPLACE(@str,',','%'' OR ProjectName LIKE ''%')+'%'''
set @Query ='SELECT * FROM ProjectDetails WHERE
     ProjectName  ' + @str
exec( @Query)

LIKE condition with comma separated values

4 Answers4

SQL Fiddle Demo