--**************************************
--     
-- Name: SQL 2 String-lists comparison
-- Description:Enables SQL Server to per
--     form a comparison between two string lis
--     ts ; the items sperated with a Delimiter
--      (same one in both strings) ; it splits 
--     the twos strings and then check if all o
--     f the first array or string's elements i
--     n the second array or not ; can be used 
--     in stored procedures/views/functions
Special Thanks TO GuyH 
http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=653&lngWId;=5
-- By: Mohamed EL-Zahaby
--
-- Inputs:2 STRINGS that you would like 
--     to split down into individual elements,a
--     nd check if the first elements in the se
--     cond array or not based on the DELIMITER
--      specified
--
-- Returns:A bit true or false; True if 
--     the second array's elements contains all
--      of the first array elements
alsto returns 1 OR true OF the first input string IS NULL 
AND returns false or 0 IF the second input IS NULL
--
--This code is copyrighted and has-- limited warranties.Please see http://
--     www.Planet-Source-Code.com/vb/scripts/Sh
--     owCode.asp?txtCodeId=793&lngWId;=5--for details.--**************************************
--     

CREATE FUNCTION dbo.CMPlist1(@String varchar(4000), @Delimiter char(1),@String1 varchar(4000))
RETURNS bit
AS
     BEGIN
    IF (@String IS null) 
    RETURN 1
    IF (@String1 IS null) 
    RETURN 0
     DECLARE @INDEX INT
     DECLARE @SLICE varchar(4000)
     DECLARE @Falgz bit
     DECLARE @Results TABLE(Items varchar(4000))
     SELECT @INDEX = 1
     WHILE @INDEX !=0
         BEGIN	
         	SELECT @INDEX = CHARINDEX(@Delimiter,@STRING1)
         	IF @INDEX !=0
         		SELECT @SLICE = LEFT(@STRING1,@INDEX - 1)
         	ELSE
         		SELECT @SLICE = @STRING1
         	INSERT INTO @Results(Items) VALUES(@SLICE)
         	SELECT @STRING1 = RIGHT(@STRING1,LEN(@STRING1) - @INDEX)
         	IF LEN(@STRING1) = 0 BREAK
     END

     SET @INDEX = 1
     SET @Falgz =1
     WHILE @INDEX !=0
         BEGIN	
         	SELECT @INDEX = CHARINDEX(@Delimiter,@STRING)
         	IF @INDEX !=0
         		SELECT @SLICE = LEFT(@STRING,@INDEX - 1)
         	ELSE
         		SELECT @SLICE = @STRING
        		IF @SLICE IN ( SELECT Items FROM @Results) SET @Falgz = @Falgz & 1
        		ELSE
        		SET @Falgz = @Falgz & 0
         	SELECT @STRING = RIGHT(@STRING,LEN(@STRING) - @INDEX)
         	IF LEN(@STRING) = 0 BREAK
    END

    --INSERT INTO @tbl (koko) VALUES(@Falgz)
    --      
     RETURN @Falgz
END