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...
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.
5 Answers
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
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:
As easy as
SELECT lpad(42::text, 4, '0')
References:
sqlfiddle:
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.
The easiest way:
ltrim(to_char(Column1, '000'))
James Sterling reports on renewable energy developments, climate policy, ecological conservation, and green tech innovations around the globe.
