Compare Comma Separated (Delimited) values in SQL Query

Last Reply one year ago By dharmendr

Posted one year 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.

Posted one year ago
Hi @Amit,
Please try the following

Check whether some string exists in a comma separated string using C# .Net

It might help you.

Cheers Andrea.

Posted one year ago Modified on one year ago

I wanna SQL Querry and that too not with contains keyword. Also both left and right contains comma separated string.

 


Posted one year 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