带填充模式的 Oracle to_char 格式编号 (FM0000)
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/32734607/
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 to_char format number with fill mode (FM0000)
提问by kev
I use the TO_CHARfunction to format number from 0001to 9999, and to fit the column size (VARCHAR2(4)) where the value is inserted (even if value is > 9999).
我用TO_CHAR函数格式编号从0001到9999，并以适合（列大小VARCHAR2(4)），其中值被插入（即使值> 9999）。
I use the function like this:
我使用这样的功能：
TO_CHAR(n, 'FM0000')
Examples that work:
有效的例子：
SELECT TO_CHAR(1, 'FM0000') FROM DUAL;
Result: 0001
结果：0001
SELECT TO_CHAR(1234, 'FM0000') FROM DUAL;
Result: 1234
结果：1234
But when I test with a value greater than 9999, I get an extra character:
但是当我用大于 9999 的值进行测试时，我得到了一个额外的字符：
SELECT TO_CHAR(12345, 'FM0000') FROM DUAL;
Result: #####
结果：#####
SELECT TO_CHAR(123456, 'FM0000') FROM DUAL;
Result: #####
结果：#####
For information, the result I expected was ####(on 4 chars).
有关信息，我预期的结果是####（4 个字符）。
To sum up:
总结：
- When the value to convert corresponds to the expected size (4), the converted value has the same length (4)
- When the value to convert is longer than the expected size (5 or more), the converted value has one more character than the expected length (5).
- 当要转换的值对应于预期大小 (4) 时，转换后的值具有相同的长度 (4)
- 当要转换的值比预期的大小（5 个或更多）长时，转换后的值比预期的长度 (5) 多一个字符。
How to explain this ?
这怎么解释？
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 文档中没有找到解释https://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements004.htm#i170559
I tried on several Oracle version (9, 10, 11) and the result is the same.
我尝试了几个 Oracle 版本（9、10、11），结果都是一样的。
The workaroundI 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.
我发现的解决方法是使用 RPAD() 函数截断结果，RPAD(TO_CHAR(n,'FM0000'), 4)但我需要了解为什么 TO_CHAR 函数是不够的。
回答by Alex Poole
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:
您的格式模型仍然必须允许值的符号。没有办法表明TO_CHAR()它永远不会是负数（如果您的价值观实际上就是这种情况）。即使是 4 位数字，格式也允许包含五个字符，正如您从列标题中看到的：
SQL> SELECT TO_CHAR(1234, 'FM0000') FROM DUAL;
TO_CH
-----
1234
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:
注意列标题是TO_CH，它是五个字符，而不是四个。如果您有一个负数（如弗洛林建议的那样），您需要额外的空间：
SQL> SELECT TO_CHAR(-1234, 'FM0000') FROM DUAL;
TO_CH
-----
-1234
Without the FMmodifier 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 allowfor the sign, even if you know there will never be negative values - there isn't any way for the format model to reflect that.
如果没有FM修饰符，您将在返回的字符串中得到一个前导空格来表示正值，LENGTH(TO_CHAR(1234, '0000'))5LENGTH(TO_CHAR(1234, 'FM0000'))也是 4，因为前导空格（通常使列中的值右对齐）被抑制。对于负值，返回字符串的长度为 5。格式模型确定返回的数据类型varchar2(5)，以允许为标志，即使你知道绝不会有负值-没有任何办法的格式模型来反映这一点。
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
There isn't anything you can do about that in the TO_CHARcall. As an alternative to your RPADworkaround, you could use SUBSTRto only get the last four characters of the formatted string:
在TO_CHAR通话中您对此无能为力。作为RPAD解决方法的替代方法，您可以使用SUBSTR仅获取格式化字符串的最后四个字符：
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
With your RPAD you keep the sign but lose the fourth significant digit:
使用您的 RPAD，您可以保留符号，但会丢失第四位有效数字：
SQL> SELECT RPAD(TO_CHAR(-1234, 'FM0000'), 4) FROM DUAL
RPAD(TO_CHAR(-12
----------------
-123
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.
这也不好。您可能不必处理负数；但是，如果您处理的数字比您预期的要大（即您得到的数字 >= 10000，而您只期望 <= 9999），那么我不确定您是否可以确定您不会看到（无效？ ) 在某些时候也是负数。无论如何，这似乎是数据问题而不是格式问题。
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:
根据您对 Ollie 的评论，对于未来的代码维护者来说，另一种可能更加明确和显而易见的方法是在 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.
如果您愿意，这也将允许您将字符串列保留为空或使用其他一些魔法值而不是####。
And another way to trim the value, which mayalso be clearer, is with CAST:
另一种修剪值的方法（也可能更清晰）是使用 CAST：
SQL> SELECT CAST(TO_CHAR(12345, 'FM0000') AS VARCHAR2(4)) FROM DUAL;
CAST
----
####
