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

Question

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

I will pass a string, such as result, sample, unknown, extras, as a parameter of the stored procedure . and then I want to get related entries by checking each phrase on this line.

Example:

Tablea

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

Now I want to find the result, sample, suspense, additional functions

Then the result should be

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

because in the first record the result matches, and in the second record the pattern matches.

Answer 1

, . (id, word).

, :

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
  )

, ?

: STUFF w/SUBSTRING .

Answer 2

, / , . , . , , :

Quick T-Sql ( )

Answer 3

, . .

, SQL Server: http://www.sommarskog.se/arrays-in-sql-2005.html

, , , , - , ( LIKE ) , . , ; .