how to write the store procedure for searching (CSV)?

Question

hi,

how can i write the store procedure for searching particular string in a column of table, for given set of strings (CSV string).

like : select * from xxx where tags like ('oscar','rahman','slumdog')

how can i write the procedure for that combination of tags.

Answer 1

First off, the use of like for exact matches is sub-optimal. Might as well use =, and if doing so, you can use the IN syntax:

select * from xxx 
where tags IN ('oscar', 'rahman', 'slumdog') 

I am guessing you are not looking for an exact match, but for any record where the tags field contains all of the tags.

This would be something like this:

select * from xxx 
where tags like '%oscar%' 
  and tags like '%rahman%'
  and tags like '%slumdog%' 

This would be not be very fast or performant though.

Think about moving this kind of logic into your application, where it is faster and easier to do.

Edit:

Following the comments - there are lots of examples on how to parse delimited strings out there. You can put these in a table and use dynamic sql to generate your query.

But, this will have bad performance and SQL Server will not be able to cache query plans for this kind of thing. As I said above - think about moving this kind of logic to application level.

Answer 2

To create a comma seperated string...

You could then apply this list to Oded example to create the LIKE parts of the WHERE cluase on the fly.

DECLARE @pos int, @curruntLocation char(20), @input varchar(2048)
SELECT @pos=0
SELECT @input = 'oscar,rahman,slumdog'
SELECT @input = @input + ','

CREATE TABLE #tempTable (temp varchar(100) )

WHILE CHARINDEX(',',@input) > 0
BEGIN
SELECT @pos=CHARINDEX(',',@input)
SELECT @curruntLocation = RTRIM(LTRIM(SUBSTRING(@input,1,@pos-1)))
INSERT INTO #tempTable (temp) VALUES (@curruntLocation)
SELECT @input=SUBSTRING(@input,@pos+1,2048)
END

SELECT * FROM #tempTable
DR0P TABLE #tempTable