Separate a string with a comma in SQL Server 2008

Question

There are two lines a and b

Line a contains a comma. I would like to split the line by comma and then go through each item.

If string b contains any element separated by comma, will return 0

(for example: a = "4,6,8" ; b = "R3799514" because line b contains 4, therefore returns 0)

How to do this using a stored procedure? Thanks in advance!

I saw the split function:

 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 select top 10 * from dbo.split('Chennai,Bangalore,Mumbai',',') 

Answer 1

The following will work -

 DECLARE @A VARCHAR (100)= '4,5,6' DECLARE @B VARCHAR (100)= 'RXXXXXX' DECLARE @RETURN_VALUE BIT = 1 --DEFAULT 1 SELECT items INTO #STRINGS FROM dbo.split(@A,',') IF EXISTS(SELECT 1 FROM #STRINGS S WHERE CHARINDEX(items, @B) > 0) SET @RETURN_VALUE = 0 PRINT @RETURN_VALUE DROP TABLE #STRINGS 

You can also use CONTAINS instead of CHARINDEX -

 IF EXISTS(SELECT 1 FROM #STRINGS S WHERE CONTAINS(items, @B)) SET @RETURN_VALUE = 0