Padding Zeros to the Left in Postgresql

Padding zeros to the left in postgreSQL

You can use the rpad and lpad functions to pad numbers to the right or to the left, respectively. Note that this does not work directly on numbers, so you'll have to use ::char or ::text to cast them:

SELECT RPAD(numcol::text, 3, '0'), -- Zero-pads to the right up to the length of 3
       LPAD(numcol::text, 3, '0')  -- Zero-pads to the left up to the length of 3
FROM   my_table

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

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

Adding leading zero if length is not equal to 10 digit using sql

In Netezza you can use LPAD:

select lpad(s.sample,10,0) as result
from (select 12345 as sample) s

    result
   -------
  0000012345

However it would be more efficient to remove the zeros like in the example below:

select cast(trim(Leading '0' from s.sample) as integer) as result
from (select '0000012345' as sample) s

    result
   -------
    12345

How to Create PostgreSQL Leading Zero Sequence (zerofill)

Create a regular sequence, then use to_char() to pad it with leading zeroes. Your data type will be char(), though, Postgres does not support zerofill for integral types.

How to add a leading zero based on range of values in postgres within an integer column

Leading zeros don't change the value of an integer, so this is a question about formatting numbers.

If you want to display the id column with leading zeros, you could do that like this:

SELECT to_char(id, '00'), key
FROM "table";

The format 00 formats the number as a two-digit string with leasing zeros. If id is greater than 99, the number cannot be formatted like this, and you will get ##.

See the documentation for details about to_char and the available formats.

Padding zeros to the end of a string and incrementing them

You could create a sequence and use something like:

your_col := your_col||'-'||LPAD(your_seq.NEXTVAL,3,'0'); /* plsql style*/

UPDATE your_table
   SET your_col = your_col||'-'||LPAD(your_seq.NEXTVAL,3,'0')
 WHERE <your_conditions>; 

however this is pretty generic since you haven't mentioned when or on what condition the update will happen.

---

Related Topics