How to remove space from SQL

Question

Example

col1        col 2       col3
300         Broad       ST

,(IsNUll((Cast(FLOOR(col1) as CHAR (7) )),'')  + ' ' + IsNull(col2,'') + ' ' + isnull(col3,'')) as col4

result i get is

300     Broad ST

what i want is

300 Broad St. 

there is 4 or 5 space between 300 and Broad

the data type for col1 is numeric and for col 2 and 3 is nvarchar. I don't want to change the data type.

Answer 1

This looks a lot like SQL Server. If so:

stuff(coalesce(' ' + Cast(floor(col1) as varchar(7)), '') +
      coalesce(' ' + col2, '') + 
      coalesce(' ' + col3, ''),
      1, 1, '') as col4