问题描述
我使用TO_CHAR函数将数字设置为从0001到9999的格式，并适合插入值的列大小(VARCHAR2(4))(即使值> 9999).
I use the TO_CHAR function to format number from 0001 to 9999, and to fit the column size (VARCHAR2(4)) where the value is inserted (even if value is > 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个字符).
For information, the result I expected was #### (on 4 chars).
总结:
- 当要转换的值对应于预期大小(4)时，转换后的值具有相同的长度(4)
- 要转换的值长于预期大小(5个或更多)时，转换后的值比预期长度(5个)多一个字符.
如何解释这一点?
我没有在Oracle文档中找到解释， https://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements004.htm#i170559
I didn't found explanation in the Oracle documentation here https://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements004.htm#i170559
我尝试了多个Oracle版本(9、10、11)，结果是相同的.
I tried on several Oracle version (9, 10, 11) and the result is the same.
解决方法，我发现是使用RPAD()函数RPAD(TO_CHAR(n,'FM0000'), 4)截断结果，但是我需要理解为什么TO_CHAR函数不够用.
The workaround I found is to truncate the result with RPAD() function RPAD(TO_CHAR(n,'FM0000'), 4) but I need to understand why the TO_CHAR function is not enough.
推荐答案
您的格式模型仍然必须允许该值的符号.没有任何方法可以指示TO_CHAR()它永远不能为负(如果实际上是您的值的情况).即使使用4位数字，格式也允许五个字符，如您在列标题中所见:
Your format model still has to allow for the sign of the value. There is no way to indicate to TO_CHAR() that it can never be negative (if that is in fact the case for your values). Even with a 4-digit number the formatting allows allows for five characters, as you can see from the column heading:
SQL> SELECT TO_CHAR(1234, 'FM0000') FROM DUAL;
TO_CH
-----
1234
请注意，列标题为TO_CH，它是五个字符，而不是四个字符.如果您使用负数(如弗罗林建议的那样)，则需要额外的空间:
Notice the column heading is TO_CH, which is five characters, not four. If you have a negative number (as Florin suggested) you need that extra space:
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)到 allow ，即使您知道永远不会出现负值-格式模型也无法通过任何方式反映该值
Without the FM modifier you get a leading space in the returned string for positive values, so LENGTH(TO_CHAR(1234, '0000')) is 5 but LENGTH(TO_CHAR(1234, 'FM0000')) is 4, because the leading space (which normally makes the values in the column right-justified) is suppressed. With a negative value the length of the returned string is 5 either way. The format model determines that the returned data type is varchar2(5) to allow for the sign, even if you know there will never be negative values - there isn't any way for the format model to reflect that.
如果您强行显示符号，您也可以看到它带有正值:
You can see it with positive values too if you force the sign to be shown:
SQL> SELECT TO_CHAR(1234, 'FMS0000') FROM DUAL;
TO_CH
-----
+1234
在TO_CHAR调用中您无能为力.作为RPAD解决方法的替代方法，您可以使用SUBSTR仅获取格式化字符串的最后四个字符:
There isn't anything you can do about that in the TO_CHAR call. As an alternative to your RPAD workaround, you could use SUBSTR to only get the last four characters of the formatted string:
SQL> SELECT SUBSTR(TO_CHAR(12345, 'FM0000'), -4) FROM DUAL
SUBSTR(TO_CHAR(1
----------------
####
但是，如果您确实有负值，则会丢失符号:
But if you do have negative values you'd lose the sign:
SQL> SELECT SUBSTR(TO_CHAR(-1234, 'FM0000'), -4) FROM DUAL
SUBSTR(TO_CHAR(-
----------------
1234
使用RPAD，您可以保留符号，但会丢失第四个有效数字:
With your RPAD you keep the sign but lose the fourth significant digit:
SQL> SELECT RPAD(TO_CHAR(-1234, 'FM0000'), 4) FROM DUAL
RPAD(TO_CHAR(-12
----------------
-123
这也不是很好.您可能不必处理负数；但是，如果您要处理的数字大于预期的数字(即，当您期望的数字仅为< = 9999时，您得到的数字> = 10000)，那么我不确定您是否可以确定不会看到(无效的)数字?)在某些时候也为负数.无论如何，这似乎是一个数据问题，而不是格式问题.
which also isn't good. You may not have to deal with negative numbers; but if you're dealing with number larger than you expect (i.e. you get a number >= 10000 when you're expecting only <= 9999) then I'm not sure you can be certain you won't see an (invalid?) negative number at some point too. This seems to be a data problem rather than a formatting problem, on some level anyway.
根据您对Ollie的评论，对于以后的代码维护者来说，另一种可能更明确，更明显的方法是在情况下将其拼写出来:
Based on your comment to Ollie, another approach which might be more explicit and obvious to future maintainers of the code is to spell it out in a CASE:
SELECT CASE WHEN n BETWEEN 0 AND 9999 THEN TO_CHAR(n, 'FM0000') ELSE '####' END FROM DUAL
如果愿意，还可以允许您将字符串列保留为空或使用其他魔法值而不是####.
Which would also allow you to leave the string column null or use some other magic value rather than ####, if you wanted to.
使用CAST:可能更清晰的另一种修整值的方法是:
And another way to trim the value, which may also be clearer, is with CAST:
SQL> SELECT CAST(TO_CHAR(12345, 'FM0000') AS VARCHAR2(4)) FROM DUAL;
CAST
----
####
这篇关于具有填充模式的Oracle to_char格式编号(FM0000)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！