SQL 在postgreSQL中向左填充零
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26379446/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Padding zeros to the left in postgreSQL
提问by Ben
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.
我对 PostgreSQL 比较陌生，我知道如何在 SQL Server 中用零填充一个数字，但我很难在 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.
我有一个数字列，其中最大位数为 3，最小值为 1：如果是一位数，则左侧有两个零，如果是 2 位数，则为 1，例如 001、058、123。
In SQL Server I can use the following:
在 SQL Server 中，我可以使用以下内容：
RIGHT('000' + cast([Column1] as varchar(3)), 3) as [Column2]
This does not exist in PostgreSQL. Any help would be appreciated.
这在 PostgreSQL 中不存在。任何帮助，将不胜感激。
回答by Mureinik
You can use the rpadand lpadfunctions 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 ::charor ::textto cast them:
您可以使用rpad和lpad函数分别向右或向左填充数字。请注意，这不适用于数字，因此您必须使用::char或::text强制转换它们：
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
回答by a_horse_with_no_name
The to_char()function is there to format numbers:
该to_char()函数用于格式化数字：
select to_char(column_1, 'fm000') as column_2
from some_table;
The fmprefix ("fill mode") avoids leading spaces in the resulting varchar. The 000simply defines the number of digits you want to have.
该fm前缀（“填充模式”）避免了产生VARCHAR前导空格。在000简单地定义你想拥有的位数。
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
更多格式图片请看手册：http:
//www.postgresql.org/docs/current/static/functions-formatting.html
回答by zerkms
As easy as
一样容易
SELECT lpad(42::text, 4, '0')
References:
参考：
sqlfiddle: http://sqlfiddle.com/#!15/d41d8/3665
sqlfiddle：http://sqlfiddle.com/#!15/d41d8/3665
