Search code examples
sql-serversearchwhere-clause

How to us a LIKE against an IN or list in SQL Server

I have a comma delimited string of keywords which I have successfully transformed into a list using a function that takes a @String and returns a TABLE(Value varchar(30))

I can now use this list in a where clause like follows:

SELECT project.*
FROM Projects project 
WHERE project.title IN (SELECT * FROM dbo.ParamsToList('.net,test'))

This matches where project.title is exactly (equal to) any one of the keywords (.net or test).

What I need is to match where title is LIKE '%' + any-keyword + '%'.

Solution

  • One way is like this:

    SELECT project.*
FROM Projects project 
WHERE EXISTS
    (
        SELECT * FROM dbo.ParamsToList('.net,test') x 
        WHERE project.title LIKE '%' + x.value + '%'
    )

    This approach will mean you don't get the same project returned multiple times, if the project matches multiple values from the params list.