Find which combination has the right subject using PATINDEX

Rajnilari2015
Posted by Rajnilari2015 under Sql Server category on | Points: 40 | Views : 1124
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
*/

Comments or Responses

Login to post response