oracle 中的值未显示前导零 [英] Values are not displaying with leading zero in oracle
问题描述
我有一个表格，其中有一列前导零的数据(数字 数据类型).当我尝试在 apex - SQL Workshop 中查询列时，它没有显示前导零.所以我需要手动转换为带前导零的显示值.但是转换时出现问题.
I have a table where i have data with leading zeros for a column(number datatype). When i try to query the column in apex - SQL Workshop, it is not displaying the leading zero. So i need to manually convert to display value with leading zeros. But there is a problem while converting.
如果我使用 to_char 显式格式化，那么它会影响正常值.例如
If i format explicitly using to_char then it is affecting the normal value.
For example
select to_char(colA,'0.99') from tab1
当值为 0.87 时会给我 0.87，但我们也有没有小数的值.在这种情况下，它会给我###.基本上我想按原样显示值(但如果值以十进制开头，则添加 0).它也不应该添加尾随零.有什么办法可以做到这一点?例如，下面将给出 661.00.但它不应该给.如果是整数，应该显示相同的值.
will give me 0.87 when value is .87 but we do have values without decimal also. in that case it will give me ###. Basically i want to display value as it is(but add 0 if value is starting with decimal). It should not add trailing zero also.Is there any way to achieve this? For example, below will give 661.00. But it should not give. If it whole number, it should display the same value.
select to_char(661,'999G999G999G999G990D00MI') from dual
推荐答案
你可以用'FM999999990D9999'这样的掩码来凑近，小数点两边各有一个合适的 9 来覆盖所有您可能拥有的价值观.
You can get close with a mask like 'FM999999990D9999', with an appropriate number of 9s each side of the decimal to cover all values you might have.
with tab1 (cola) as (
select 0.87 from dual
union select 661 from dual
union select 661.87 res from dual
union select 1.5 res from dual
)
select cola, to_char(cola, 'FM999999990D9999')
from tab1;
COLA TO_CHAR(COLA,'F
---------- ---------------
.87 0.87
1.5 1.5
661 661.
661.87 661.87
FM 删除尾随零和前导空格(包括 +/- 符号的标称空格).
The FM removes trailing zeros and leading spaces (including a nominal space for a +/- sign).
要去掉尾随的小数点标记，您也需要将其剪掉:
To get rid of the trailing decimal marker too you need to trim it off:
with tab1 (cola) as (
select 0.87 from dual
union select 661 from dual
union select 661.87 res from dual
union select 1.5 res from dual
)
select cola, rtrim(to_char(cola, 'FM999999990D9999'), to_char(0, 'FMD'))
from tab1;
我在这两个部分都坚持使用 D；您可以在两者中使用固定的 . 这样您就不需要第二个 to_char() 调用来转换它，但您可能希望它由会话控制 -无论哪种方式，它都需要保持一致.
I've stuck with D in both parts of that; you could use a fixed . in both so you don't need the second to_char() call to convert that, but you may want it to be controlled by the session - either way it needs to be consistent.
如果您不知道需要包含多少个 9，您可以根据小数点分隔符前后的位数为每个数字生成一个定制的格式掩码:
If you don't know how many 9s you need to include, you could generate a bespoke format mask for every number, based on how many digits there are before and after the decimal separator:
with tab1 (cola) as (
select 0.87 from dual
union all select 661 from dual
union all select 661.87 res from dual
union all select 1.5 res from dual
union all select 0.00045354543 from dual
)
select cola,
'FM' || lpad('0', length(trunc(cola)), '9')
|| case when trunc(cola) != cola
then 'D' || rpad('9', length(cola - trunc(cola)) - 1, '9')
end as format_mask,
to_char(cola,
'FM' || lpad('0', length(trunc(cola)), '9')
|| case when trunc(cola) != cola
then 'D' || rpad('9', length(cola - trunc(cola)) - 1, '9')
end) as result
from tab1;
COLA FORMAT_MASK RESULT
--------------- -------------------- --------------------
.87 FM0D99 0.87
661 FM990 661
661.87 FM990D99 661.87
1.5 FM0D9 1.5
.00045354543 FM0D99999999999 0.00045354543
这依赖于隐式转换，但似乎适用于正数、负数和零.它不需要修剪结果，因为小数点分隔符 D 只包含在非整数中.
This relies on implicit conversion but seems to work for positive, negative and zero. It doesn't need to trim the result because the decimal separator D is only included at all for non-integers.
这篇关于oracle 中的值未显示前导零的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
