sql - oracle sql TO_CHAR 函数在某些情况下添加尾随空白

Question

我有一个包含数字列的表，我正在尝试使用 oracle sql 函数 to_char 对其进行格式化。根据列的显示方式，结果是否会出现尾随空白。

SELECT 
  '[',TO_CHAR(ABS(Balance), 'FM0000000000000000V00'),']',
  '[' || TO_CHAR(ABS(Balance),'FM0000000000000000V00') || ']'
FROM Accounts

会得到以下结果:

[;000000000749460366 ;];[000000000749460366]
[;000000008751094792 ;];[000000008751094792]
[;000000000000050696 ;];[000000000000050696]
[;000000000000000000 ;];[000000000000000000]

我希望在这两种情况下得到相同的结果，但我不明白为什么会有差异。我有这些差异的原因是什么？

非常感谢，皮埃尔-伊夫

Answer 1

它不会在 值 中添加尾随空格 - 如果您检查它的长度，它仍然是 18，如果是，那么连接后的版本也会有一个空格。该列的元数据决定最大可能长度为 19，如果原始余额值超过 16 位，这似乎是正确的:

set colsep ;
with accounts(balance) as (
  select 749460366 from dual
  union all select 1234567890123456 from dual
  union all select 12345678901234567 from dual
)
SELECT 
  '[',TO_CHAR(ABS(Balance), 'FM0000000000000000V00') as balance,']',
  '[',length(TO_CHAR(Balance, 'FM0000000000000000V00')) as length,']',
  '[' || TO_CHAR(ABS(Balance),'FM0000000000000000V00') || ']'
FROM Accounts;

';BALANCE            ;';';    LENGTH;';'['||TO_CHAR(ABS(BALA
-;-------------------;-;-;----------;-;---------------------
[;000000074946036600 ;];[;        18;];[000000074946036600] 
[;123456789012345600 ;];[;        18;];[123456789012345600] 
[;###################;];[;        19;];[###################]

提供的格式无法显示 17 位数字的值，它用哈希值代替；但有趣的是，其中有 19 个，无论实际余额有多大。

FM 修饰符抑制正值的前导空格；但是如果你有一个负的 16 位数字，你仍然需要 19 位数字来显示它，因为减号会出现。 您知道由于 ABS() 调用，该值不能为负，但 TO_CHAR() 函数不知道，所以它仍然必须允许列宽中的减号。

如果您真的从应用程序运行查询/转换，例如通过 JDBC，您返回的字符串(少于 17 位，在 ABS() 之后)将是 18 个字符，并且不会有(不存在的)尾随空格。

您的客户端(或者至少是 SQL*Plus 和 SQL Developer)正在根据它认为可能的最宽值来格式化列宽。你可以覆盖它:

column balance format A18

with accounts(balance) as (
  select 749460366 from dual
  union all select 1234567890123456 from dual
  union all select 12345678901234567 from dual
  union all select 123456789012345678 from dual
)
SELECT 
  '[',TO_CHAR(ABS(Balance), 'FM0000000000000000V00') as balance,']',
  '[',length(TO_CHAR(Balance, 'FM0000000000000000V00')) as length,']',
  '[' || TO_CHAR(ABS(Balance),'FM0000000000000000V00') || ']'
FROM Accounts;

';BALANCE           ;';';    LENGTH;';'['||TO_CHAR(ABS(BALA
-;------------------;-;-;----------;-;---------------------
[;000000074946036600;];[;        18;];[000000074946036600] 
[;123456789012345600;];[;        18;];[123456789012345600] 
[;##################;];[;        19;];[###################]
 ;#                 ; ; ;          ; ;                     

[;##################;];[;        19;];[###################]
 ;#                 ; ; ;          ; ;                     

... 这使得非空格消失，但如果/当原始值超过 16 位数字时，它会把额外的散列包装到下一行时会变得更加困惑。

您还可以将结果显式转换为您期望的长度:

clear columns

with accounts(balance) as (
  select 749460366 from dual
  union all select 1234567890123456 from dual
  union all select 12345678901234567 from dual
  union all select 123456789012345678 from dual
)
SELECT 
  '[',CAST(TO_CHAR(ABS(Balance), 'FM0000000000000000V00') as varchar2(18)) as balance,']',
  '[',length(TO_CHAR(Balance, 'FM0000000000000000V00')) as length,']',
  '[' || TO_CHAR(ABS(Balance),'FM0000000000000000V00') || ']'
FROM Accounts;

';BALANCE           ;';';    LENGTH;';'['||TO_CHAR(ABS(BALA
-;------------------;-;-;----------;-;---------------------
[;000000074946036600;];[;        18;];[000000074946036600] 
[;123456789012345600;];[;        18;];[123456789012345600] 
[;##################;];[;        19;];[###################]
[;##################;];[;        19;];[###################]