Thursday, April 21, 2011

Why is TO_CHAR function adding a space?

A user at my work requested to generate auto number concatenated with '/' and the current year, as follow:- 001/2011,002/2011 ...etc

I used the following to do that

SELECT TO_CHAR(1,'000')||'/'||to_char(sysdate,'RRRR') sn
FROM dual

SN
---------
001/2011


But I noticed that the output serial has an extra leading space, then I decided to add TRIM to the code as follow:-

SELECT TRIM(TO_CHAR(1,'000')||'/'||to_char(sysdate,'RRRR')) sn
FROM dual

SN
---------
001/2011


Till now the user requirement is done, but I didn't understand why TO_CHAR returned an extra leading space

I found the answer in this link

the reason for the extra leading space is for the potential minus sign as shown below

select '['||to_char(1, '000')||'/'||to_char(sysdate,'RRRR')||']' sn1, 
'['||to_char(-1, '000')||'/'||to_char(sysdate,'RRRR')||']' sn2, 
'['||to_char(1,'FM000')||'/'||to_char(sysdate,'RRRR')||']' sn3 
from dual
/

SN1         SN2         SN3
----------- ----------- -----------
[ 001/2011] [-001/2011] [001/2011]

No comments:

Post a Comment