SQL Search column for each variable in CSV string

Question

I have a variable passed to stored procedure Ex:

@keywords = 'val1, val3, val5'

And i'm trying to see if column named Title contain any of them in it

Ex: Title1 - 'Hello val1'
    Title2 - 'Hello val3'   
    Title3 - 'Hello val1, val3'  
    Title4 - 'Hello' 

SO my results should return values

Title
------
Hello val1
Hello val3
Hello val1, val3

Is this possible to use LIKE or any other function/method?

Answer 1

You need to split the CSV into rows (see Arrays and Lists in SQL Server 2005 and Beyond for variuos techniques how). I'll assume that you create dbo.ufnSplitRows based on this

Then JOIN using LIKE

SELECT *
FROM
    MYtable M
    JOIN
    dbo.ufnSplitRows (@CSV) C ON M.Title LIKE '%' + C.SplitValue + '%'

By the way, it will run poorly because of the leading '%' at least

Answer 2

If you make some assumptions about how the query string is stored, then yes (though it's not terribly efficient):

Assumption: the string will be stored with every item separated by a comma, then a space. (This is what you posted in your question)

select * from YourTable where 
    (@keywords like KeyColumn + ', %') or
    (@keywords like '%, ' + KeyColumn + ', %') or
    (@keywords like '%, ' + KeyColumn)