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
The easiest way:
ltrim(to_char(Column1, '000'))
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
As easy as
SELECT lpad(42::text, 4, '0')
References:
- http://www.postgresql.org/docs/current/static/functions-string.html
sqlfiddle: http://sqlfiddle.com/#!15/d41d8/3665