How to compare two column values which are comma separated values?

Question

I have one table with specific columns, in that there is a column which contains comma separated values like test,exam,result,other.

I will pass a string like result,sample,unknown,extras as a parameter to the stored procedure. and then I want to get the related records by checking each and every phrase in this string.

For Example:

TableA

ID        Name                Words
1         samson              test,exam,result,other
2         john                sample,no query
3         smith               tester,SE

Now I want to search for result,sample,unknown,extras

Then the result should be

ID        Name                Words
1         samson              test,exam,result,other
2         john                sample,no query

because in the first record result matched and in the second record sample matched.

Answer 1

Personally I think you'd want to look at your application/architecture and think carefully about whether you really want to do this in the database or the application. If it isn't appropriate or not an option then you'll need to create a custom function. The code in the article here should be easy enough to modify to do what you want:

Quick T-Sql to parse a delimited string (also look at the code in the comments)

Answer 2

That's not a great design, you know. Better to split Words off into a separate table (id, word).

That said, this should do the trick:

set nocount on
declare @words varchar(max) = 'result,sample,unknown,extras'

declare @split table (word varchar(64))
declare @word varchar(64), @start int, @end int, @stop int

-- string split in 8 lines
select @words += ',', @start = 1, @stop = len(@words)+1
while @start < @stop begin
  select
    @end   = charindex(',',@words,@start)
  , @word  = rtrim(ltrim(substring(@words,@start,@end-@start)))
  , @start = @end+1
  insert @split values (@word)
end

select * from TableA a
where exists (
  select * from @split w
  where charindex(','+w.word+',',','+a.words+',') > 0
  )

May I burn in DBA hell for providing you this!

Edit: replaced STUFF w/ SUBSTRING slicing, an order of magnitude faster on long lists.

Answer 3

Like the others have already said -- what you have there is a bad design. Consider using proper relations to represent these things.

That being said, here's a detailed article about how to do this using SQL Server: http://www.sommarskog.se/arrays-in-sql-2005.html

One thing no one has covered so far, because it's often a very bad idea -- but then, you are already working with a bad idea, and sometimes two wrongs make a right -- is to extract all rows that match ANY of your strings (using LIKE or some such) and doing the intersection yourself, client-side. If your strings are fairly rare and highly correlated, this may work pretty well; it will be god-awful in most other cases.