Filter Record from table if data stored in comma (,) seprated.

CREATE TABLE TBL_USER

(

 id int,

 LanguageKnow varchar(300)

)

insERT INTO TBL_USER VALUES

(1,'HI,EN'),

(2,'HI,EN,FR')

I WANT TO FILTER THOSE RECORDS THAT  CONTAINS 'HI,FR'


Query to filter-

DECLARE @ListValue varchar(50)='FR'

SELECT * FROM  TBL_USER T WHERE

1 = case when isnull(@ListValue,0)='' then 1 when isnull(@ListValue,0)!='0'

and (exists(select A.Item from SplitString(@ListValue,',') A

inner join (select Item from SplitString(T.LanguageKnow,',')) B on A.Item=B.Item))then 1 else 0 end



You need to create below  spilt string function- 

---Function ———–

CREATE FUNCTION [dbo].[SplitString]

(

@SplitStr nvarchar(max),

@SplitChar nvarchar(5)

)

RETURNS @RtnValue table

(

Item nvarchar(max)

)

AS

BEGIN

Declare @Count int

Set @Count = 1

While (Charindex(@SplitChar,@SplitStr)>0)

Begin

Insert Into @RtnValue (Item)

Select

Item = ltrim(rtrim(Substring(@SplitStr,1,Charindex(@SplitChar,@SplitStr)-1)))

Set @SplitStr = Substring(@SplitStr,Charindex(@SplitChar,@SplitStr)+1,len(@SplitStr))

Set @Count = @Count + 1

End

Insert Into @RtnValue (Item)

Select Item = ltrim(rtrim(@SplitStr))

Return

END