Compare comma separated values with comma separated values in sql

CREATE FUNCTION [dbo].[Split](@String varchar(8000), @Delimiter char(1))

returns @temptable TABLE (items varchar(8000))

as

begin

        declare @idx int

        declare @slice varchar(8000)

   

        select @idx = 1

                if len(@String)<1 or @String is null  return

   

        while @idx!= 0

        begin

                set @idx = charindex(@Delimiter,@String)

                if @idx!=0

                        set @slice = left(@String,@idx - 1)

                else

                        set @slice = @String

               

                if(len(@slice)>0)

                        insert into @temptable(Items) values(@slice)

                set @String = right(@String,len(@String) - @idx)

                if len(@String) = 0 break

        end

return

end

GO

--function end

GO

--now check below sample

--table variable to hold data

declare @table table

(

        id int,[key] varchar(100)

)

insert into @table

select 1,'session, state' union all

select 2,'a,b,c' union all

select 3,'hi, hello'

--parameter value

declare @data varchar(1000)

set @data='b,c'

--select query to find data

select distinct id

from @table

CROSS APPLY dbo.Split(strkeywords,',') AS AA

CROSS APPLY dbo.Split(@data,',') AS BB

WHERE AA.items=BB.items