Compare Comma Separated (Delimited) values in SQL Query

Last Reply 3 months ago By dharmendr

Posted 3 months ago

Hi,

I have a scenario where user provided a comma separated string like 'amit,sanjay,sumit' and I want to match where each comma separated value is subset of another data in table.

For e.g.

Table 1

Sl.No        ColumnName

1.            sumit,sanjay,amit,ajay

2.            amit,sumit,sanjay

3.            amit,sumit,akshay

 

I want a query which would return Sl.No 1 and 2 as 'amit,sanjay,sumit' is exists in those two rows and not on the third row.

You are viewing reply posted by: dharmendr 3 months ago.
Posted 3 months ago

Hi Amit,

Check this sample query and correct your query by refering it.

For split string function refer below article.

Split function in SQL Server Example: Function to Split Comma separated (Delimited) string in SQL Server 2005, 2008 and 2012

SQL

DECLARE @Test AS TABLE(SiNo INT,ColumnName VARCHAR(100))
INSERT INTO @Test VALUES(1,'sumit,sanjay,amit,ajay')
INSERT INTO @Test VALUES(2,'amit,sumit,sanjay')
INSERT INTO @Test VALUES(3,'amit,sumit,akshay')

DECLARE @CommaString VARCHAR(50), @stringCount INT
SET @CommaString = 'amit,sanjay,sumit'
SELECT @stringCount = COUNT(Item) FROM dbo.SplitString(@CommaString,',')


DECLARE @Result VARCHAR(MAX)
SELECT @Result = COALESCE(@Result + ' and ' ,'') + CONVERT(VARCHAR(20),SiNo)
FROM (SELECT SiNo
FROM (
	SELECT CASE WHEN COUNT(SiNo) = @stringCount THEN SiNo END SiNo 
	FROM (
			SELECT t.SiNo,x.Item
			FROM @Test t
			CROSS APPLY(SELECT Item FROM dbo.SplitString(t.ColumnName,',')) x
			) a
	WHERE a.Item IN (SELECT Item FROM dbo.SplitString(@CommaString,','))
	GROUP BY SiNo) f
WHERE f.SiNo IS NOT NULL)g

SELECT ''''+ @CommaString + ''' is exist in Sl.No ' + @Result Result

Output

Result
'amit,sanjay,sumit' is exist in Sl.No 1 and 2