covert number strings with leading zeros to numeric value with leading zero in postgres 12

Question

i am new to PostgreSQL.i want to convert string '0480' to number 0480 .i am using casting operator to do this,

db=# select '0480':: numeric;

 numeric 
---------
     480
(1 row)

but here I am expecting 0480 number. Then i tried to_number function in PostgreSQL,

db=# select to_number('0480','9999');

 to_number 
-----------
       480
(1 row)

so how is this possible? how can I convert number strings with leading zeros to numeric value with leading zero.

also some outputs of to_char function, little bit confusing?

db=# select to_char(0480,'9999');

 to_char 
---------
   480
(1 row)

db=# select to_char(0480,'09999');

 to_char 
---------
  00480
(1 row)

Using PostgreSQL 12.3 on Debian

Numbers don't have leading zeros...when you add leading zeros, numbers become strings. Please explain what you want to achieve. — Bill Jetzer
– Bill Jetzer, Commented Jul 9, 2020 at 12:33
There is no such thing as number 0480. Numbers don't have leading zeros. String representations of numbers can have the leading zeros, which are typically used to make them all the same length. — Ken White
– Ken White, Commented Jul 9, 2020 at 13:22
i just want to store phone numbers,so as @KenWhite mentioned , i have to store it as text — SHARON XAVIER
– SHARON XAVIER, Commented Jul 17, 2020 at 6:54

user330315 · Accepted Answer · 2020-07-09 12:49:06Z

Numbers don't have leading zeros. 0480 is exactly the same number as 480 - just like 00000001 is exactly the same number as 1

The only way to get leading zeros, is to convert the number to a string and add the desired number of leading zeros.

some outputs of to_char function, little bit confusing?

Not, not at all.

to_char(0480,'9999') converts the number 480 to a 4 character string. The 9 indicates that if there is no value for that digit, it can be "dropped".

Quote from the manual

9 - digit position (can be dropped if insignificant)
0 - digit position (will not be dropped, even if insignificant)

Leading zeros are always insignificant (from a mathematical perspective).

to_char(480,'0000') will do you what you expect: a four digit string with a leading zero.
Similarly to_char(1,'0000') will return 0001.

Note that by default, all output of to_char() is right-aligned with leading spaces. If you don't want that, you should use the FM modifier: e.g. to_char(1,'FM0000')

Collectives™ on Stack Overflow

covert number strings with leading zeros to numeric value with leading zero in postgres 12

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related