user1587872 user1587872 - 1 year ago 62
SQL Question

SQL: Count of matches in WHERE clause

i need to find the number of expressions matched in WHERE clause for a SELECT statement. Sample data and table is as follows:

declare @var1 varchar(10), @var2 varchar(10), @var3 varchar(10)
set @var1 = 'a'
set @var2 = null
set @var3 = 'c'
insert into tempTable values(1, 'a', 'b', 'c')
insert into tempTable values(2, 'a', 'e', 'c')
insert into tempTable values(3, 'g', 'b', 'c')

select id from tempTable
where ISNULL(@var1, colA) = colA
AND ISNULL(@var2, colB) = colB
AND ISNULL(@var3, colC) = colC

The output should be like this:

id MatchingCount
1 2 (because @var1 and @var3 are matched)
2 2 (because @var1 and @var3 are matched)


Answer Source
select id, cast ((case when ISNULL(@var1, colA) = colA then 1 else 0 end) as int) +cast ((case when ISNULL(@var2, colB) = colB then 1 else 0 end) as int) +
cast ((case when ISNULL(@var3, colC) = colC then 1 else 0 end) as int) as MatchCount
from tempTable

Use Case

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download