SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE T(
   col1 varchar(5),
   col2 varchar(5),
   col3 varchar(5),
   col4 varchar(5)
);

INSERT INTO T VALUES ( 'a'   , 'b'     ,  NULL  , NULL  );
INSERT INTO T VALUES ( NULL, NULL  ,  NULL  , NULL  );
INSERT INTO T VALUES ( 'a'   , NULL  ,  NULL  , NULL  );
INSERT INTO T VALUES ( NULL, 'b'     ,  'c'     , 'd'     );
INSERT INTO T VALUES ( NULL, NULL  ,  NULL  , NULL  );

with cte as 
 (
   select  
      ConcateColumn = STUFF(  COALESCE('* ' + RTRIM(col1),'') 
                             +COALESCE('* ' + RTRIM(col2),'') 
                             +COALESCE('* ' + RTRIM(col3),'') 
                             +COALESCE('* ' + RTRIM(col4),'') 
                       , 1, 2, '') 
   FROM T
 )
 
select ConcateColumn,
   coalesce(len(ConcateColumn) - len(replace(ConcateColumn, '*', '')) + 1, 0)
from cte 
;

select  (SELECT count(val)
        FROM   (VALUES(col1),(col2),(col3), (col4)) v (val)) 'COUNT',
          ConcateColumn = STUFF(
                              COALESCE('* ' + RTRIM(col1),'') 
                             +COALESCE('* ' + RTRIM(col2),'') 
                             +COALESCE('* ' + RTRIM(col3),'') 
                             +COALESCE('* ' + RTRIM(col4),'') 
                       , 1, 2, '') 
FROM T

ConcateColumn	(No column name)
a* b	2
null	0
a	1
b* c* d	3
null	0

COUNT	ConcateColumn
2	a* b
0	null
1	a
3	b* c* d
0	null

Warning: Null value is eliminated by an aggregate or other SET operation.