CODEWITHSUNDEEP
sqlpostgresql
user8927599
user8927599

Reputation:

Append leading zeros to a field

I want to append leading zeros when the length of the integer is lesser than 7 digits. If the length of the integer is more than or equal to 7 digits it should return the actual integer. I've tried sql like below

select to_char(123456, 'fm0000000');
to_char 
---------
0123456
(1 row)

When the number of digits was > 7 it returns below string

select to_char(12345678, 'fm0000000');
to_char 
---------
#######
(1 row)

How can I get the exact integer when the number of digits greater than 7? Thanks in advance

Upvotes: 0

Views: 129

Answers (2)

Petro K
Petro K

Reputation: 190

Use lpad function plus CASE statement:

select
  case
    when length(123456::text) < 7 then lpad(123456::text, 7, '0')
    else 123456::text
  end;

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269993

You can use lpad():

select (case when len(123412345::text) >= 8 then 123412345::text
             else lpad(123412345::text, 8, '0')
        end)

Upvotes: 3

Related Questions