Search code examples
sqlsql-servertrimcoalesce

How to remove leading zero(s) from SQL

I want to remove leading zeros from SQL table. I'm joining 3 column as one column. example

col1         col2        col3
00000        S           Gaskin Road
             N           Broad Street
00001                    John Rolfe Road

what i want the result to be:

1 0 S Gaskin Road or just S Gaskin Road 
2   N Broad Street 
3 1 John Rolfe Road

here is the script that i join 3 column 

   ,COALESCE(CASE WHEN col1 = '' THEN '' ELSE col1 + ' ' END, '')  + 
   COALESCE(CASE WHEN col2 = '' THEN '' ELSE col2 + ' ' END, '')  + 
   COALESCE(CASE WHEN col3 = '' THEN '' ELSE col3 + ' ' END, '') as allCol
Solution

  • cast col1 to int so the leading zeroes are removed and cast the int to varchar for concatenation.

    COALESCE(CASE WHEN col1 = '' THEN '' ELSE cast(cast(col1 as int) as varchar(255))+ ' ' END, '')  + 
COALESCE(CASE WHEN col2 = '' THEN '' ELSE col2 + ' ' END, '')  + 
COALESCE(CASE WHEN col3 = '' THEN '' ELSE col3 + ' ' END, '')