sql - Combine 3 Column when one of the value is empty( not null) remove extra space -

data

col1    col2    col3  5121     w        river road  5512     empty    pine road  

query 1

select ltrim(isnull(col1+' ','')+ isnull(col2+' ', '') + isnull(col3+' ','') col4 ... 

query 2

select ltrim(coalesce(col1+' ','')+ coalesce(col2+' ', '') + coalesce(col3+' ','') col4 ... 

for both results values

5121 w river road ( looks )  5512  pine road ( space col 2 ) 

thank

the problem have 3 cases

• null
• length = 0
• length > 0

demo

create table table1     ([col1] varchar(5), [col2] varchar(5), [col3] varchar(20)) ;  insert table1     ([col1], [col2], [col3]) values     ('5121', 'w', 'river road'),     ('5512', null, 'pine road'),     ('3333', '', 'death valley') ;  select coalesce(case when col1 = '' '' else col1 + ' ' end, '')  +         coalesce(case when col2 = '' '' else col2 + ' ' end, '')  +         coalesce(case when col3 = '' '' else col3 + ' ' end, '')          table1   

output

original op , jhon version work ok null , length > 0 fail length = 0