How to combine mulitple column adding '-' and removing '-' with value is NULL

Question

this is my Data

col1    col2    col col3    col4    col5    col6 
42      A       11  18      89      16      empty
42      B       12  empty   89      14      C
36      8       9   empty   empty   2       empty 

this is the Script I'm running

select col1 + COALESCE ([col2]+'-','')
+COALESCE([col3]+'-','')+COALESCE([col4]+'-','')
+COALESCE([col5]+'-','')+COALESCE([col6],'') as totalCol 
FROM ...  

This is what I get

totalCol
42A-11-18-89-16-
42B-12- -89-14-C
368-9- - -2 -

This is what I want

totalCol
42A-11-18-89-16
42B-12-89-14-C
368-9-2

Answer 1

run a replace of "- " to "" after your script

also you have to remember that nulls and blank space are two different things.

Updated script

select ltrim(rtrim(replace(replace(
col1 + COALESCE ([col2]+'-','')
+COALESCE([col3]+'-','')+COALESCE([col4]+'-','')
+COALESCE([col5]+'-','')+COALESCE([col6],'') + ' ','--','-'),
'- ','')))
as totalCol 
FROM ...  

or assuming only blank space and not nulls

select ltrim(rtrim(replace(
[col1] + [col2]+'-' + [col3]+'-' + [col4]+'-' + [col5]+'-' + [col6] + ' '
'- ','')))
as totalCol 
FROM ...