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+' ','') as col4 from ...

query 2

Select LTRIM(COALESCE(Col1+' ','')+ COALESCE(col2+' ', '') + COALESCE(col3+' ','') as col4 from ...

for both results i get this values

5121 w river road ( this looks good ) 
5512  pine road ( i get extra space for col 2 )

Thank you

Solution

The problem is you have three cases

NULL
Length = 0
Length > 0

DEMO

CREATE TABLE Table1
    ([col1] varchar(5), [col2] varchar(5), [col3] varchar(20))
;

INSERT INTO Table1
    ([col1], [col2], [col3])
VALUES
    ('5121', 'w', 'river road'),
    ('5512', null, 'pine road'),
    ('3333', '', 'death valley')
;

SELECT COALESCE(CASE WHEN col1 = '' THEN '' ELSE col1 + ' ' END, '')  + 
       COALESCE(CASE WHEN col2 = '' THEN '' ELSE col2 + ' ' END, '')  + 
       COALESCE(CASE WHEN col3 = '' THEN '' ELSE col3 + ' ' END, '')        

FROM Table1

OUTPUT

Original OP and Jhon version work OK with NULL and length > 0 but fail with length = 0