0

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?

rs.
  • 26,707
  • 12
  • 68
  • 90

2 Answers2

4

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

gbn
  • 422,506
  • 82
  • 585
  • 676
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)
Adam Robinson
  • 182,639
  • 35
  • 285
  • 343
  • You'd need leading % too + start/end of string conditions – gbn Jul 01 '10 at 18:52
  • 1
    @gbn: Thanks; I had already started correcting right after I posted it. – Adam Robinson Jul 01 '10 at 18:54
  • 1
    yep, now it's as ugly as I expected :-) – gbn Jul 01 '10 at 18:57
  • I guess this will not work for my case. I want to search each word in @keyword in keycolumn – rs. Jul 01 '10 at 18:59
  • @rs: have you tried this? It should work too. It's just less clear in my opinion but this does not mean it doesn't work. – gbn Jul 01 '10 at 19:03
  • @gbn: Agreed, it's ugly. The only advantage is being self-contained; you don't have to create a table-valued function (there are myriad string-splitting function scripts available). – Adam Robinson Jul 01 '10 at 19:24
  • I tried it didn't work will this work for string 'Hello val1 how are you doing?' – rs. Jul 01 '10 at 19:45