Hi Amit,
Check this sample query and correct your query by refering it.
For split string function refer below article.
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 |
