CODEWITHSUNDEEP
sqlpostgresql
Ben
Ben

Reputation: 2725

Padding zeros to the left in postgreSQL

I am relatively new to PostgreSQL and I know how to pad a number with zeros to the left in SQL Server but I'm struggling to figure this out in PostgreSQL.

I have a number column where the maximum number of digits is 3 and the min is 1: if it's one digit it has two zeros to the left, and if it's 2 digits it has 1, e.g. 001, 058, 123.

In SQL Server I can use the following:

RIGHT('000' + cast([Column1] as varchar(3)), 3) as [Column2]

This does not exist in PostgreSQL. Any help would be appreciated.

Upvotes: 172

Views: 188830

Answers (5)

Schneems
Schneems

Reputation: 15828

I was frustrated with LPAD and TO_CHAR since they don't work if your string goes above the minimum length. I can't speak for how efficient this is but you can chain FORMAT to give you a minimum sized string, and then replace any spaces with zeros using REPLACE

with sample_numbers (nr) as (
    values (1),(11),(100),(1000)
)
SELECT REPLACE(FORMAT('%3s', nr), ' ', '0')
from sample_numbers;
 replace
---------
 001
 011
 100
 1000
(4 rows)

Versus with other methods TO_CHAR:

with sample_numbers (nr) as (
    values (1),(11),(100),(1000)
)
SELECT to_char(nr, 'fm000')
from sample_numbers;
 to_char
---------
 001
 011
 100
 ###
(4 rows)

(Notice the last value is ### and not 1000)

And LPAD:

with sample_numbers (nr) as (
    values (1),(11),(100),(1000)
)
SELECT LPAD(nr::varchar(255), 3, '0')
from sample_numbers;
 lpad
------
 001
 011
 100
 100
(4 rows)

(Notice the last value is 100 and not 1000)

There are other methods involving using CASE but I like that the REPLACE & FORMAT combo doesn't need any variable repetition.

Upvotes: 9

Vladimir Pankov
Vladimir Pankov

Reputation: 457

The easiest way:

ltrim(to_char(Column1, '000'))

Upvotes: 2

Mureinik
Mureinik

Reputation: 311163

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

Upvotes: 297

user330315
user330315

Reputation:

The to_char() function is there to format numbers:

select to_char(column_1, 'fm000') as column_2
from some_table;

The fm prefix ("fill mode") avoids leading spaces in the resulting varchar. The 000 simply defines the number of digits you want to have.

psql (9.3.5)
Type "help" for help.

postgres=> with sample_numbers (nr) as (
postgres(>     values (1),(11),(100)
postgres(> )
postgres-> select to_char(nr, 'fm000')
postgres-> from sample_numbers;
 to_char
---------
 001
 011
 100
(3 rows)

postgres=>

For more details on the format picture, please see the manual:
http://www.postgresql.org/docs/current/static/functions-formatting.html

Upvotes: 78

zerkms
zerkms

Reputation: 254916

As easy as

SELECT lpad(42::text, 4, '0')

References:

sqlfiddle: http://sqlfiddle.com/#!15/d41d8/3665

Upvotes: 22

Related Questions