Bharat
Bharat

Reputation: 6095

SQL query for complex filtering

I have SQL schema something like this.

select Id, AccountId, GradeLevelStart, GradeLevelEnd from AccountProfile

Which contains data as shown in this snapshot.

enter image description here

Here GradeLevelStart, and GradeLevelEnd is Id of Gradelevels. see below query and it's data.

select Id, Name, Abbreviation from GradeLevel

enter image description here

I have created one filter which works using between operator. Flow is something like this,

Here is the query for it.

DECLARE @Grades NVARCHAR(100)
SET @Grades = '3'

    select AccountId, GradeLevelStart, GradeLevelEnd, glStart.Name, glStart.Abbreviation, glEnd.Name, glEnd.Abbreviation 
    from AccountProfile 
        left join GradeLevel glStart on glStart.ID = GradeLevelStart
        left join GradeLevel glEnd on glEnd.ID = GradeLevelEnd
    where @Grades IS NULL OR @Grades BETWEEN GradeLevelStart and GradeLevelEnd

What I am looking for?

This all works fine till now, but it's not yet taking multiple @Grades for filtering.

So what I want is User gives input in a format like below query and system should return all the accounts which has GradeLevelStart and GradeLevelEnd in between this @Grades

SET @Grades = '3,13,'

What I tried so far?

Any help would be really appreciated.

Upvotes: 0

Views: 49

Answers (1)

Thiyagu
Thiyagu

Reputation: 1340

Use String Split Function to achieve this:

DECLARE @Grades NVARCHAR(100)
SET @Grades = '3,13'

select AccountId, GradeLevelStart, GradeLevelEnd, glStart.Name, glStart.Abbreviation, 
glEnd.Name, glEnd.Abbreviation 
from AccountProfile 
left join GradeLevel glStart on glStart.ID = GradeLevelStart
left join GradeLevel glEnd on glEnd.ID = GradeLevelEnd
OUTER APPLY STRING_SPLIT(@Grades,',') s
where @Grades IS NULL OR (s.value) BETWEEN GradeLevelStart and GradeLevelEnd

Also, You can achieve the Same by using XML Nodes Instead of STRING SPLIT:

For your ref:

declare @S varchar(100) = 'Hello John Smith'

select 
n.r.value('.', 'varchar(50)')
from (select cast('<r>'+replace(@S, ' ', '</r><r>')+'</r>' as xml)) as s(XMLCol)
cross apply s.XMLCol.nodes('r') as n(r)

Upvotes: 2

Related Questions