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

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