Riddle me this: how long is the following string:
to_char (10, '0999')
Did you answer four? Then, sorry to say, you are wrong. The correct answer is five.
SQL> select '['||to_char (10, '0999') ||']'
2 from dual
3 /
'['||TO
-------
[ 0010]
As you can see, there is a space at the beginning of the string. This space is reserved for the sign (either a plus "+" or a minus "-" ).
If you don't want this in your output, you can do something like this
SQL> select '['||to_char (10, 'fm0999') ||']'
2 from dual
3 /
'['||TO
-------
[0010]
And as you can see, the space is no longer there.
What if you do want the sign to appear in the output? Use this format mask
SQL> select '['||to_char (10, 's0999') ||']'
2 from dual
3 /
'['||TO
-------
[+0010]
Link
Oracle Documentation on Format Models
I'm wondering if there is a typo in the -.2 examples of these:
ReplyDelete11.2
and
10.2
No, I don't believe there is a typo in the documentation. The TO_CHAR functions is used to turn a number into a CHAR (fixed length character string). With the Format mask (the zero's and the nines) you specify how you want the number to be shown. The format that is used in the examples is "90.99", which means that there can be a maximum of two digits before the period (.) with at least a zero before the period.
ReplyDeleteAnd you get an extra character for the sign (the plus or the minus. So with the format mask as "90.99" you get a string with 6 characters. Two before the period; two after the period; the period itself, and a placeholder for the sign.
The thing that might seem odd is that the sign (the minus in the documentation example) is placed directly before the zero, with a leading space.