CODEWITHSUNDEEP
sqlsql-servert-sql
JM1
JM1

Reputation: 1715

TSQL - How to search a string in multiple rows?

I am unsure how to get SQL to do what I am trying to figure out. I have one student, and depending on their grade level, would be in one of three schools (schoolid).

I need to return only the StudentID and SchoolID of the school the student is currently in, based on the grade level. How do I search in this way please?

enter image description here

Desired Result (the student is in grade 12, therefore the SchoolID is 500):

enter image description here

SQL Fiddle

Thank you.

Upvotes: 0

Views: 89

Answers (3)

paparazzo
paparazzo

Reputation: 45106

where GradesOfferedInSchool like '%' + cast(StudentCurrentGrade as varchar(max)) + '%'

But that is a terrible data design
Should not have multiple values in one column

This is still just a hack as this would find '12b'

If this is a view then look for normalized data
better but still a hack

 where GradesOfferedInSchool like '% ' + cast(StudentCurrentGrade as varchar(max)) + ', %'
    or GradesOfferedInSchool like cast(StudentCurrentGrade as varchar(max)) + ', %'
    or GradesOfferedInSchool like '%, ' + cast(StudentCurrentGrade as varchar(max))
    or GradesOfferedInSchool = cast(StudentCurrentGrade as varchar(max))

Upvotes: 2

n00bs
n00bs

Reputation: 50

Weird why you do INT on [StudentCurrentGrade] where 'PK' and 'KK' are the option for it anyway: 

SELECT [StudentID]
     , [SchoolID]
     , [GradesOfferedInSchool]
     , [StudentCurrentGrade]
  FROM [dbo].[StudentFeederPatterns]
 WHERE CHARINDEX( CAST([StudentCurrentGrade] AS VARCHAR)
                  , [GradesOfferedInSchool]) > 0

Upvotes: 0

Stuart
Stuart

Reputation: 764

This works for me using Microsoft SQL Server. The CAST is needed otherwise it was throwing an error due to the different datatypes of the fields.

SELECT
 StudentID,
 SchoolID

FROM dbo.StudentFeederPatterns
WHERE GradesOfferedInSchool LIKE '%' + CAST(StudentCurrentGrade AS VARCHAR) + '%'

SQL Fiddle

Upvotes: 1

Related Questions