Let us suppose we have a recordset as under
DECLARE @T TABLE(ID INT IDENTITY,Subjects VARCHAR(50))
INSERT INTO @T VALUES('Physics,Chemistry,Maths'),('English,Maths,Computer Science'),('Maths,English,Computer Science,Biology')
SELECT *
FROM @T
/*
ID Subjects
1 Physics,Chemistry,Maths
2 English,Maths,Computer Science
3 Maths,English,Computer Science,Biology
*/
And we are interested to find out the subset of records which contains the subject English. The below query will do so
SELECT *
FROM @T
WHERE PATINDEX('%English%',Subjects) > 0
/*
ID Subjects
2 English,Maths,Computer Science
3 Maths,English,Computer Science,Biology
*/