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?
SQL Fiddle code: (I have trouble getting the schema to build without error.)
CREATE TABLE #T1 (
INSERT INTO #T1
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
select t.* from #t1 t where ',' + t.schoolgrades + ',' like '%,' + mostrecentgrade + ',%';