Oracle中to_char函数处理数字时前面不够位数补零

时间:2013-08-03
简介:数据库|自打我用to_char来转换数字就发现了这个问题,即转换结果字符串最前面多了一个空格。我一直用trim再处理一下,没有关注过原因。今天上网查了一下,看到了一个说法及解决方案。Oracleto_char...
自打我用to_char来转换数字就发现了这个问题,即转换结果字符串最前面多了一个空格。我一直用trim再处理一下,没有关注过原因。今天上网查了一下,看到了一个说法及解决方案。 

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个空格)

TAGS:Oracleto_char函数

上一编:在一个表上建立了两个索引或多个索引的情况

下一编:Oracle 10g 一些基本的查询语句

NewHot手机版