Oracle to_char():数字转字符串,结果添加空格:
select to_char(12,'00') from dual字符串是' 12',前面多了一个空格,
select length(to_char(12,'00')) from dual
返回结果是:3返回的字符串总是前面有个空格。原因:
那个空格位置是放符号的,正的数字就空了,负的就是一个‘-’号而没有空格。
这是网上流传的原因:
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.
Track back
解决办法:
select trim(to_char(12,'00')) from dual
或者
select to_char(12,'fm00') from dual
总结:
select to_char(12,'00000') from dual结果为:
00012(1个空格)
select to_char(12,'99999') from dual结果为:
12(4个空格)
select to_char(-12,'00000') from dual结果为:
-00012(无个空格)
select to_char(-12,'99999') from dual结果为:
-12(3个空格)