It's happens because
s specifier makes presence of sign mandatory and places it on specified position. Even you can put sign as last symbol in string, look at number format documentation or just try:
to_char(10,'S999') "10 S999",
to_char(-10,'S999') "-10 S999",
to_char(0,'S999') "0 S999",
to_char(10,'999S') "10 999S",
to_char(-10,'999S') "-10 999S",
to_char(0,'999S') "0 999S"
According to documentation:
Negative return values automatically contain a leading negative sign
and positive values automatically contain a leading space unless the
format model contains the MI, S, or PR format element.
you always get a "minus" char for negative values at start of a string, so there are no need for
S format specifier at most of the cases.
But if you need for some purpose to always place a sign before or after digits, then you can use
S format specifier.
If you ask "why
+0 and not
-0" - it's just "by design" :)
And some words about "design":
A long time ago (far far away ... :) ) Oracle decide to use special values of data types to indicate null values. For character data it's empty string (that's also source of two string types -
varchar2) and for numeric types it's
If you look at specification of computer number format you can find, that for a signed number one bit always reserved to store sign. Zero value of this bit treated as '+' and value 1 as
Two representations of zero
+0 are same from the point of view of math and one of them can be eliminated without loosing any functionality. So, Oracle designers decide to always store zero value as
+0 and treat
May be behavior changed in modern versions of Oracle, but for compatibility reasons zero value normalization remains unchanged till today and you always got
+0 in string representation.