SQL Oracle - 为什么将数字转换为 TO_CHAR 时数字的前导零会消失
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6695604/
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
Oracle - Why does the leading zero of a number disappear when converting it TO_CHAR
提问by contactmatt
In Oracle, when converting a number with a leading zero to a character, whydoes the leading number disappear? Is this logic Oracle specific, or specific to SQL?
在Oracle中，将前导零的数字转换为字符时，为什么前导数字消失了？这种逻辑是 Oracle 特有的，还是 SQL 特有的？
Example:
例子：
SELECT TO_CHAR(0.56) FROM DUAL;
/* Result = .56 */
采纳答案by DCookie
It's the default formatting that Oracle provides. If you want leading zeros on output, you'll need to explicitly provide the format. Use:
这是 Oracle 提供的默认格式。如果您希望输出前导零，则需要明确提供格式。用：
SELECT TO_CHAR(0.56,'0.99') FROM DUAL;
or even:
甚至：
SELECT TO_CHAR(.56,'0.99') FROM DUAL;
The same is true for trailing zeros:
对于尾随零也是如此：
SQL> SELECT TO_CHAR(.56,'0.990') val FROM DUAL;
VAL
------
0.560
The general form of the TO_CHAR conversion function is:
TO_CHAR 转换函数的一般形式为：
回答by Vadzim
I was looking for a way to format numbers without leading or trailing spaces, periods, zeros (except one leading zero for numbers less than 1 that should be present).
我正在寻找一种方法来格式化数字而没有前导或尾随空格、句点、零（除了应该存在的小于 1 的数字的前导零）。
This is frustrating that such most usual formatting can't be easily achieved in Oracle.
令人沮丧的是，这种最常见的格式在 Oracle 中无法轻松实现。
Even Tom Kyte only suggested long complicated workaroundlike this:
甚至Tom Kyte 也只提出了像这样长期复杂的解决方法：
case when trunc(x)=x
then to_char(x, 'FM999999999999999999')
else to_char(x, 'FM999999999999999.99')
end x
But I was able to find shorter solution that mentions the value only once:
但是我能够找到更短的解决方案，该解决方案仅提及一次该值：
rtrim(to_char(x, 'FM999999999999990.99'), '.')
This works as expectedfor all possible values:
这对所有可能的值都按预期工作：
select
to_char(num, 'FM99.99') wrong_leading_period,
to_char(num, 'FM90.99') wrong_trailing_period,
rtrim(to_char(num, 'FM90.99'), '.') correct
from (
select num from (select 0.25 c1, 0.1 c2, 1.2 c3, 13 c4, -70 c5 from dual)
unpivot (num for dummy in (c1, c2, c3, c4, c5))
) sampledata;
| WRONG_LEADING_PERIOD | WRONG_TRAILING_PERIOD | CORRECT |
|----------------------|-----------------------|---------|
| .25 | 0.25 | 0.25 |
| .1 | 0.1 | 0.1 |
| 1.2 | 1.2 | 1.2 |
| 13. | 13. | 13 |
| -70. | -70. | -70 |
Still looking for even shorter solution.
仍在寻找更短的解决方案。
There is a shortening approarch with custom helper function:
有一个带有自定义辅助函数的缩短方法：
create or replace function str(num in number) return varchar2
as
begin
return rtrim(to_char(num, 'FM999999999999990.99'), '.');
end;
But custom pl/sql functions have significant performace overheadthat is not suitable for heavy queries.
但是自定义 pl/sql 函数具有显着的性能开销，不适合繁重的查询。
回答by mkb
Seems like the only way to get decimal in a pretty (for me) form requires some ridiculous code.
似乎以漂亮的（对我来说）形式获得十进制的唯一方法需要一些荒谬的代码。
The only solution I got so far:
到目前为止我得到的唯一解决方案：
CASE WHEN xy>0 and xy<1 then '0' || to_char(xy) else to_char(xy)
xyis a decimal.
xy是一个小数。
xy query result
0.8 0.8 --not sth like .80
10 10 --not sth like 10.00
回答by rawberto
That only works for numbers less than 1.
这仅适用于小于 1 的数字。
select to_char(12.34, '0D99') from dual;
-- Result: #####
This won't work.
这行不通。
You could do something like this but this results in leading whitespaces:
你可以做这样的事情，但这会导致领先的空格：
select to_char(12.34, '999990D99') from dual;
-- Result: ' 12,34'
Ultimately, you could add a TRIM to get rid of the whitespaces again but I wouldn't consider that a proper solution either...
最终，您可以添加一个 TRIM 来再次去除空格，但我也不认为这是一个合适的解决方案......
select trim(to_char(12.34, '999990D99')) from dual;
-- Result: 12,34
Again, this will only work for numbers with 6 digits max.
同样，这仅适用于最多 6 位的数字。
Edit: I wanted to add this as a comment on DCookie's suggestion but I can't.
编辑：我想将此添加为对 DCookie 建议的评论，但我不能。
回答by rawberto
Try this to avoid to_char limitations:
试试这个以避免 to_char 限制：
SELECT
regexp_replace(regexp_replace(n,'^-\'||s,'-0'||s),'^\'||s,'0'||s)
FROM (SELECT -0.89 n,RTrim(1/2,5) s FROM dual);
回答by Jo?l
Should work in all cases:
应该适用于所有情况：
SELECT regexp_replace(0.1234, '^(-?)([.,])', '') FROM dual
回答by Tarkeshwar Prasad
Below format try if number is like
下面的格式试试如果数字是这样的
ex 1 suppose number like 10.1 if apply below format it will be come as 10.10
ex 1 假设数字像 10.1 如果应用以下格式，它将成为 10.10
ex 2 suppose number like .02 if apply below format it will be come as 0.02
ex 2 假设数字像 0.02 如果适用于以下格式，它将成为 0.02
ex 3 suppose number like 0.2 if apply below format it will be come as 0.20
ex 3 假设数字像 0.2 如果应用以下格式，它将成为 0.20
to_char(round(to_number(column_name)/10000000,2),'999999999990D99') as column_name
to_char(round(to_number(column_name)/10000000,2),'999999999990D99') 作为 column_name
