SPLIT STRING THEN GET COMMON DATA

----WE HAVE TWO STRINGS THE WE FIND COMMON FIELDS
-----FIRST WE CREATE TEMP TABLES THEN USING SPLIT FUNCTIONS TO INSERT DATA INTO TEMP TABLES
----THEN JOIN OR INTERSECT TO GET COMMOM FIELDS

CREATE  PROC USP_STRINGS(@STR1 VARCHAR(500),@STR2 VARCHAR(500))
AS
BEGIN
SET @STR1=@STR1+','
SET @STR2=@STR2+','
 DECLARE @SP1 INT=1
 DECLARE @PP1 INT=1
 DECLARE @SP2 INT=1
 DECLARE @PP2 INT=1
 select @SP1= CHARINDEX(',',@STR1,@PP1)
 select @SP2= CHARINDEX(',',@STR2,@PP1)
 CREATE TABLE #TEMP1 (NAME VARCHAR(500))
 CREATE TABLE #TEMP2 (NAME VARCHAR(500))
WHILE (@SP1>0)
BEGIN
INSERT INTO #TEMP1
SELECT SUBSTRING (@STR1,@PP1,@SP1-@PP1)
SET @PP1=@SP1+1
SELECT @SP1 = CHARINDEX (',',@STR1,@PP1)
END
WHILE (@SP2>0)
BEGIN
INSERT INTO #TEMP2
SELECT SUBSTRING (@STR2,@PP2,@SP2-@PP2)
SET @PP2=@SP2+1
SELECT @SP2 = CHARINDEX (',',@STR2,@PP2)
END
SELECT T1.NAME FROM #TEMP1 T1 INNER JOIN #TEMP2 T2 ON T1.NAME=T2.NAME
 END

 EXEC USP_STRINGS  @STR1='MANJU,RAMU,ANJI,HARI,BABU,DEVI',
 @STR2='MANJU,HFHHF,GFM,HARI,FGNGFG'

OUTPUT

MANJU
HARI


SELECT CHARINDEX(',','MANJU,RAMU,ANJI,HARI,BABU,DEVI',1)

SELECT SUBSTRING('MANJU,RAMU,ANJI,HARI,BABU,DEVI',1,5)