to_char前面多出空格


用to_char对数字时前面会多出一个空格。
SQL> select to_char(10,'00') from dual;


TO_
---
 10      --前面有一个空格，这是一个正数的符号位


SQL> select length(to_char(10,'00')) from dual;


LENGTH(TO_CHAR(10,'00'))
------------------------
                       3       --所以长度是3
                       


解决方法 ：要么加 fm 要么加 trim


SQL> select length(to_char(10,'fm00')) from dual;


LENGTH(TO_CHAR(10,'FM00'))
--------------------------
                         2


SQL> select length(trim(to_char(10,'00'))) from dual;


LENGTH(TRIM(TO_CHAR(10,'00')))
------------------------------
                             2                       
                             


网上有：http://www.huomo.cn/database/article-ce7a.html
FM Fill mode. Oracle uses blank characters to fill format elements to a constant width equal to the largest element for the relevant format model in the current session language. For example, when NLS_LANGUAGE is AMERICAN, the largest element for MONTH is SEPTEMBER, so all values of the MONTH format element are padded to 9 display characters. This modifier suppresses blank padding in the return value of the TO_CHAR function: In a datetime format element of a TO_CHAR function, this modifier suppresses blanks in subsequent character elements (such as MONTH) and suppresses leading zeroes for subsequent number elements (such as MI) in a date format model. Without FM, the result of a character element is always right padded with blanks to a fixed length, and leading zeroes are always returned for a number element. With FM, which suppresses blank padding, the length of the return value may vary. In a number format element of a TO_CHAR function, this modifier suppresses blanks added to the left of the number, so that the result is left-justified in the output buffer. Without FM, the result is always right-justified in the buffer, resulting in blank-padding to the left of the number.