CODEWITHSUNDEEP
sqlsql-servertrimcoalesce
john
john

Reputation: 105

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

Upvotes: 3

Views: 13557

Answers (5)

user10891817
user10891817

Reputation: 1

For Amazon Redshift:

TRIM(LEADING '0' FROM colname)

Upvotes: 0

maulik kansara
maulik kansara

Reputation: 1107

COALESCE(CASE WHEN col1 = '' THEN '' ELSE SUBSTRING(col1, PATINDEX('%[^0]%', col1+'.'), LEN(col1))+ ' ' END, '')  + 
COALESCE(CASE WHEN col2 = '' THEN '' ELSE col2 + ' ' END, '')  + 
COALESCE(CASE WHEN col3 = '' THEN '' ELSE col3 + ' ' END, '')

Upvotes: 0

Marc Guillot
Marc Guillot

Reputation: 6465

You can define your own function to remove all the leading zeros :

CREATE FUNCTION RemoveLeadingZeros(@value varchar(255)) RETURNS varchar(255)
AS
BEGIN
  while substring(@value, 1, 1) = '0' and datalength(@value) > 1 
  begin
    set @value = substring(@value, 2, 255)
  end

  return @value;
END
GO

So the concatenation of your 3 fields now will be :

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

Upvotes: 1

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

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, '')

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1270391

One method uses patindex(). My first thought on how to write this is:

  ((case when col1 is null or col1 = '' or col1 = '000000' then ''
         else substring(col1, patindex('%[^0]%', col1), 6) + ' '
    end) +
   (case when col2 is null or col2 = '' then ''
         else col2 + ' '
    end) +
   (case when col3 is null or col3 = '' then ''
         else col3
    end)
   ) as allcol

If you already need to use case, I don't see an advantage to mixing in coalesce().

Upvotes: 1

Related Questions