ORACLE 数字转字符串
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15875666/
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 convert number to string
提问by BeHunter
Need some help in converting numbers:
在转换数字时需要一些帮助：
select to_char(a, '99D99')
, to_char(a, '90D99')
from
(
select 50 a from dual
union
select 50.57 from dual
union
select 5.57 from dual
union
select 0.35 from dual
union
select 0.4 from dual
Will result in:
会导致：
1 ,35 0,35
2 ,40 0,40
3 5,57 5,57
4 50,00 50,00
5 50,57 50,57
But how to make my output like:
但是如何使我的输出像：
- 0,35
- 0,4
- 5,57
- 50
- 50,57
- 0,35
- 0,4
- 5,57
- 50
- 50,57
I need 0before comma, but not after.
我需要0在逗号之前，但不需要在逗号之后。
回答by Alex Poole
Using the FMformat model modifierto get close, as you won't get the trailing zeros after the decimal separator; but you will still get the separator itself, e.g. 50.. You can use rtrimto get rid of that:
使用FM格式模型修饰符来接近，因为你不会得到小数点分隔符后的尾随零；但您仍然会得到分隔符本身，例如50.. 你可以用它rtrim来摆脱它：
select to_char(a, '99D90'),
to_char(a, '90D90'),
to_char(a, 'FM90D99'),
rtrim(to_char(a, 'FM90D99'), to_char(0, 'D'))
from (
select 50 a from dual
union all select 50.57 from dual
union all select 5.57 from dual
union all select 0.35 from dual
union all select 0.4 from dual
)
order by a;
TO_CHA TO_CHA TO_CHA RTRIM(
------ ------ ------ ------
.35 0.35 0.35 0.35
.40 0.40 0.4 0.4
5.57 5.57 5.57 5.57
50.00 50.00 50. 50
50.57 50.57 50.57 50.57
Note that I'm using to_char(0, 'D')to generate the character to trim, to match the decimal separator - so it looks for the same character, ,or ., as the first to_charadds.
请注意，我正在使用to_char(0, 'D')生成要修剪的字符，以匹配小数点分隔符 - 因此它会查找与第一个添加的字符相同的字符,或。.to_char
The slight downside is that you lose the alignment. If this is being used elsewhere it might not matter, but it does then you can also wrap it in an lpad, which starts to make it look a bit complicated:
轻微的缺点是你失去了对齐。如果在其他地方使用它可能无关紧要，但它确实如此，那么您也可以将其包装在 中lpad，这开始使它看起来有点复杂：
...
lpad(rtrim(to_char(a, 'FM90D99'), to_char(0, 'D')), 6)
...
TO_CHA TO_CHA TO_CHA RTRIM( LPAD(RTRIM(TO_CHAR(A,'FM
------ ------ ------ ------ ------------------------
.35 0.35 0.35 0.35 0.35
.40 0.40 0.4 0.4 0.4
5.57 5.57 5.57 5.57 5.57
50.00 50.00 50. 50 50
50.57 50.57 50.57 50.57 50.57
回答by araknoid
This should solve your problem:
这应该可以解决您的问题：
select replace(to_char(a, '90D90'),'.00','')
from
(
select 50 a from dual
union
select 50.57 from dual
union
select 5.57 from dual
union
select 0.35 from dual
union
select 0.4 from dual
);
Give a look also as this SQL Fiddlefor test.
也看看这个SQL Fiddle进行测试。
