# Check how many null in a row

#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 col2
0+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``````

#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)``````