Check how many null in a row

Transact-SQL
1

declare @a table(col1 int, col2 int, col3 int)
insert into @a
values
(10, null, null),
(10, 10, null),
(null, 10, null),
(null, null, 10),
(null, 10, 10),
(10, null, 10),
(null, null, null),
(10, 10, 10)

select *
from @a
cross apply(
select SUM(col)
from (
select col10+1
union all
select col20+1
union all
select col3*0+1
)src(col)
)src(col)

select *, LEN(COALESCE(LEFT(col1, 1), '') + COALESCE(LEFT(col2, 1), '') + COALESCE(LEFT(col3, 1), ''))
from @a

any new better tricks?

2

Your second select looks short and effective.
Alternative to that one, could be:

select *
      ,isnull(sign(len(col1)),0)
      +isnull(sign(len(col2)),0)
      +isnull(sign(len(col3)),0)
       as col
  from @a
1 Like
3

How about (since you have three columns)

select 3*COUNT(*) - 
       COUNT(col1) - 
       COUNT(col2) -
       COUNT(col3)
from @a
4

hi gbritton, its about how many null value by row not by column =P

5

OK -- this will do it:

select 3-#notnulls as #numnulls
from @a
cross apply (
    select count(col)
    from (values (col1), (col2), (col3)) v(col)
) _(#notnulls)
1 Like