CODEWITHSUNDEEP
sqlsql-servertrimisnull
john
john

Reputation: 105

How to remove space from SQL

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.

Upvotes: 0

Views: 50

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

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

Upvotes: 1

Related Questions