oracle 如何在多个oracle中显示前导零
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25968792/
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
How to display the leading zero's in a number of oracle
提问by Ramu Pedada
I have an oracle column(artnr) contains a length of 1 which is of type number(9). I want to update the number as following...
我有一个 oracle 列（artnr），它的长度为 1，类型为 number(9)。我想更新数字如下...
Example :
例子 ：
If number is 0 then it should be 00000 If number is 1 then it should be 00001 If number is 12 the it should be 00012
如果数字是 0 那么它应该是 00000 如果数字是 1 那么它应该是 00001 如果数字是 12 它应该是 00012
Remember : here 00000,0000, and 00012 are of number datatypes
请记住：这里 00000,0000 和 00012 是数字数据类型
The following are the methods I have tried but failed..
以下是我尝试过但失败的方法..
UPDATE pitb.toestel b
SET b.artnr = LPAD (b.artnr, 5, 0)
WHERE b.idinventaris = 403743;
Failed because Lpad can only be applied on strings
失败，因为 Lpad 只能应用于字符串
UPDATE pitb.toestel b
SET b.artnr = TO_NUMBER (TO_CHAR (artnr, '00009'), '00009')
WHERE b.idinventaris = 403743;
Still failed, because to_number will not display the leading zero's. It will only consider from first number
仍然失败，因为 to_number 不会显示前导零。它只会从第一个数字考虑
Anyone, could you please suggest me something which will solve this scenario..
任何人，你能不能给我建议一些可以解决这种情况的东西..
sql is preferrable than pl/sql solution
sql 比 pl/sql 解决方案更可取
回答by Lalit Kumar B
If number is 0 then it should be 00000 If number is 1 then it should be 00001 If number is 12 the it should be 00012
Remember : here 00000,0000, and 00012 are of number datatypes
如果数字是 0 那么它应该是 00000 如果数字是 1 那么它应该是 00001 如果数字是 12 它应该是 00012
请记住：这里 00000,0000 和 00012 是数字数据类型
Firstly, Numbers don't have leading zero's. So, when you store the NUMBER values, you let them behave like NUMBERs. it is only when you want to display them, you can use LPADand add the leading zeroes. Which conevrts the number to a string with leading zeroes.
首先，数字没有前导零。因此，当您存储 NUMBER 值时，您可以让它们表现得像 NUMBER。只有当您想显示它们时，您才可以使用LPAD并添加前导零。它将数字转换为带有前导零的字符串。
So, no need to update the table. Use LPADto display them the way you want.
因此，无需更新表。用于LPAD以您想要的方式显示它们。
SQL> WITH DATA AS
2 ( SELECT 1 ID FROM DUAL UNION ALL
3 SELECT 11 ID FROM DUAL
4 )
5 SELECT
6 LPAD(ID,5, 0) id
7 FROM DATA
8 /
ID
-----
00001
00011
To avoid, implicit data type conversion, use TO_CHARbefore applying LPAD.
为避免隐式数据类型转换，请TO_CHAR在应用之前使用LPAD。
回答by Giorgi Orvelashvili
select to_char(x,'00000') from dual;
回答by René Nyffenegger
If you reallywant to store those numbers with preceeding zeroes then you must change the datatype to varchar2. Then you can apply to_char( artnr , 'fm00009')in the update statement. Of course, this might come with unintended consequences. Go for this solution at your own risk.
如果你真的想用前面的零存储这些数字，那么你必须将数据类型更改为 varchar2。然后就可以to_char( artnr , 'fm00009')在update语句中申请了。当然，这可能会带来意想不到的后果。自行承担风险选择此解决方案。
You might also consider creating a view that zero-paddes the number "on the fly" when you select from that view.
您还可以考虑创建一个视图，当您从该视图中进行选择时，该视图将“即时”补零。
回答by cosgiu83
In my case the goal was calculate the sum of the values of the different currencies, but the issue was created by the data type of the field VALUE that is VARCHAR2(255 BYTE). I have found this solution, for handle the problem of the leading zero:
在我的例子中，目标是计算不同货币的价值总和，但问题是由字段 VALUE 的数据类型造成的，即 VARCHAR2(255 BYTE)。我找到了这个解决方案，用于处理前导零的问题：
SELECT ID_OUT
, CASE WHEN REPLACE(SUM(REPLACE(VALUE, '.', ',')), ',', '.') LIKE '-.%' THEN REPLACE(REPLACE(SUM(REPLACE(VALUE, '.', ',')), ',', '.'), '-.', '-0.')
WHEN REPLACE(SUM(REPLACE(VALUE, '.', ',')), ',', '.') LIKE '.%' THEN REPLACE(REPLACE(SUM(REPLACE(VALUE, '.', ',')), ',', '.'), '.', '0.')
ELSE REPLACE(SUM(REPLACE(VALUE, '.', ',')), ',', '.')
END AS VALORE
, LOB
, 'TOTAL' CURRENCY
, COUNTRY
FROM QRT_OUT_DATI
WHERE (CURRENCY != 'Total' AND CURRENCY != 'TOTAL')
GROUP BY ID_OUT, LOB, COUNTRY, CURRENCY
ORDER BY LOB;
