postgresql 如果长度不等于 10 位，则使用 sql 添加前导零
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19728976/
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
Adding leading zero if length is not equal to 10 digit using sql
提问by moe
I am trying to join 2 tables but my problem is that one of the table has 10 digit number and the other one may have 10 or less digit number. For this reason, i am loosing some data so i would like to do is check the length first if the length is less than 10 digit then i want to add leading zeros so i can make it 10 digit number. I want to do this when i am joining this so i am not sure if this is possible. Here is an example if i i have 251458 in the TABLE_WITHOUT_LEADING_ZERO then i want to change it like this: 0000251458. Here is what i have so far:
我正在尝试加入 2 张桌子，但我的问题是其中一张桌子有 10 位数字，而另一张桌子可能有 10 位或更少的数字。出于这个原因，我丢失了一些数据，所以我想做的是先检查长度，如果长度小于 10 位，然后我想添加前导零，这样我就可以使它成为 10 位数字。我想在加入时这样做，所以我不确定这是否可行。这是一个例子，如果我在 TABLE_WITHOUT_LEADING_ZERO 中有 251458 然后我想像这样改变它：0000251458。这是我到目前为止的内容：
select ACCT_NUM, H.CODE
FROM TABLE_WITH_LEEDING_ZERO D, TABLE_WITHOUT_LEADING_ZERO H
WHERE substring(D.ACCT_NUM from position('.' in D.ACCT_NUM) + 2) = cast (H.CODE as varchar (10))
thanks
谢谢
回答by bma
Another alternative:
另一种选择：
SELECT TO_CHAR(12345,'fm0000000000');
to_char
------------
0000012345
回答by Niederee
In Netezza you can use LPAD:
在 Netezza 中，您可以使用 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
