JM1 JM1 - 1 year ago 42
SQL Question

TSQL - How do you search a column containing a string using another column?

I am trying to isolate the school ID based on the most recent grade column. This shows which schools a student is zoned for. I'm not sure how to search a string based on a column result. What's the best way to isolate the schoolid?

Thank you.


enter image description here

Desired Result:

enter image description here

SQL Fiddle code: (I have trouble getting the schema to build without error.)

StudentID varchar(10),
AddressID varchar(10),
SchoolID varchar(10),
SchoolGrades varchar(100),
MostRecentGrade varchar(10),



Answer Source

First, you have a very poor data structure. You shouldn't be storing lists in a single column. You should have a separate table with one row for each grade in each school. Or, have max and min columns for the grades in a school -- assuming that schools have consecutive grades.

But, you can do what you want using like:

select t.*
from #t1 t
where ',' + t.schoolgrades + ',' like '%,' + mostrecentgrade + ',%';