具有填充模式的Oracle to_char格式编号（FM0000）

一尘不染

具有填充模式的Oracle to_char格式编号（FM0000）

sql

我使用该TO_CHAR函数来格式化从0001到的数字9999，并适合VARCHAR2(4)插入值的列大小（）（即使value> 9999）。

我使用这样的功能：

TO_CHAR(n, 'FM0000')

有效的示例：

SELECT TO_CHAR(1, 'FM0000') FROM DUAL;

结果：0001

SELECT TO_CHAR(1234, 'FM0000') FROM DUAL;

结果：1234

但是，当我 使用大于9999的值进行 测试时 ，会得到一个额外的字符 ：

SELECT TO_CHAR(12345, 'FM0000') FROM DUAL;

结果：#####

SELECT TO_CHAR(123456, 'FM0000') FROM DUAL;

结果：#####

对于信息，我期望的结果是####（4个字符）。

总结一下：

当要转换的值对应于预期大小（4）时，转换后的值具有相同的长度（4）
当要转换的值大于预期的大小（5个或更多）时，转换后的值比预期的长度（5个）多一个字符。

怎么解释呢？

在Oracle文档 https://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements004.htm#i170559上没有找到说明

我尝试了几种Oracle版本（9、10、11），结果是相同的。

*我发现 *的解决方法是使用RPAD（）函数截断结果，RPAD(TO_CHAR(n,'FM0000'), 4)但是我需要了解为什么TO_CHAR函数不够用。

阅读 169

2021-05-23

共1个答案

一尘不染

您的格式模型仍然必须允许该值的符号。没有办法表明TO_CHAR()它永远不会为负（如果实际上是您的值的情况）。即使使用4位数字，格式也允许五个字符，如您从列标题中所看到的：

SQL> SELECT TO_CHAR(1234, 'FM0000') FROM DUAL;

TO_CH
-----
1234

请注意，列标题是TO_CH，这是五个字符，而不是四个字符。如果您有一个负数（如弗洛林建议），则需要额外的空间：

SQL> SELECT TO_CHAR(-1234, 'FM0000') FROM DUAL;

TO_CH
-----
-1234

没有FM修饰符，您将在返回的字符串中得到一个正数的前导空格，所以LENGTH(TO_CHAR(1234, '0000'))正数为5，但是LENGTH(TO_CHAR(1234, 'FM0000'))为4，因为前导空格（通常使列中的值右对齐）被抑制。如果值为负，则返回的字符串的长度均为5。格式模型确定返回的数据类型varchar2(5)将
允许使用符号，即使您知道永远不会出现负值-格式模型也无法通过任何方式反映这一点。

如果您强制显示符号，您也可以看到它带有正值：

SQL> SELECT TO_CHAR(1234, 'FMS0000') FROM DUAL;

TO_CH
-----
+1234

您在TO_CHAR通话中无能为力。作为RPAD解决方法的替代方法，您可以SUBSTR用来仅获取格式化字符串的最后四个字符：

SQL> SELECT SUBSTR(TO_CHAR(12345, 'FM0000'), -4) FROM DUAL

SUBSTR(TO_CHAR(1
----------------
####

但是，如果您确实有负值，那么您将失去符号：

SQL> SELECT SUBSTR(TO_CHAR(-1234, 'FM0000'), -4) FROM DUAL

SUBSTR(TO_CHAR(-
----------------
1234

使用您的RPAD，您可以保留符号，但会丢失第四个有效数字：

SQL> SELECT RPAD(TO_CHAR(-1234, 'FM0000'), 4) FROM DUAL

RPAD(TO_CHAR(-12
----------------
-123

这也不是很好。您可能不必处理负数；但是，如果您要处理的数字大于预期的数字（即，当您期望的数字仅为<= 9999时，得到的数字> =
10000），那么我不确定您是否可以确定不会看到（无效的数字？）也为负数。无论如何，这似乎是一个数据问题，而不是格式问题。

根据您对Ollie的评论，另一种可能对代码的未来维护者更明确和明显的方法是在CASE中将其说明：

SELECT CASE WHEN n BETWEEN 0 AND 9999 THEN TO_CHAR(n, 'FM0000') ELSE '####' END FROM DUAL

如果需要，还可以使字符串列保留为空或使用其他不可思议的值，而不是####。

修剪值（也可能更清楚）的另一种方法是使用CAST：

SQL> SELECT CAST(TO_CHAR(12345, 'FM0000') AS VARCHAR2(4)) FROM DUAL;

CAST
----
####

2021-05-23