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?

gbn · Accepted Answer · 2010-07-01 18:48:14Z

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

answered Jul 1, 2010 at 18:48

gbn

435k84 gold badges602 silver badges690 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Girish K G Over a year ago

what is this dbo.ufnSplitRows?

gbn Over a year ago

@GirishKG: a generic name for some user split function in SQL Server. There are several available on Google, here on SO, or in the link I sent

Adam Robinson · Accepted Answer · 2010-07-01 18:50:43Z

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)

answered Jul 1, 2010 at 18:50

Adam Robinson

186k36 gold badges294 silver badges351 bronze badges

7 Comments

gbn Over a year ago

You'd need leading % too + start/end of string conditions

Adam Robinson Over a year ago

@gbn: Thanks; I had already started correcting right after I posted it.

gbn Over a year ago

yep, now it's as ugly as I expected :-)

rs. Over a year ago

I guess this will not work for my case. I want to search each word in @keyword in keycolumn

gbn Over a year ago

@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.

|

Collectives™ on Stack Overflow

SQL Search column for each variable in CSV string

2 Answers 2

2 Comments

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related