Zero conversion in sql

oracle

Char convertion in Oracle have a pattern: to_char(number,'pattern') and provide option "s" to take sign to result, for ex: select to_char(10,'S999') from dual; will return +10, but when i check select to_char(0,'S999') from dual; it return +0
Anyone could explain why 🙂

Best Answer

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:

select
  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"
from dual

SQLFiddle

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" :)

Update
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 - varchar and varchar2) and for numeric types it's -0 value.

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 and +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 -0 as null.
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.

Related Question