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?