Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
2  
Seems that you mixed up TO_CHAR and TO_NUMBER in your question. –  ThinkJet Jul 2 '13 at 21:03
    
@ThinkJet You right, sorry for my mistake, could you explain why :-) –  Happy Girl Jul 3 '13 at 2:31

1 Answer 1

up vote 1 down vote accepted

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.

share|improve this answer
    
Thank for your answer, It isn't the first time i get the answer by design :-) –  Happy Girl Jul 4 '13 at 13:47
    
@HappyGirl Ok, answer updated with design explanation, but I'am not an Oracle designer :-) –  ThinkJet Jul 4 '13 at 14:33
    
@ThinkJet do you have a source for the information on Oracle storing numeric nulls as -0? I have doubts about it. –  David Aldridge Jul 4 '13 at 17:53
    
@DavidAldridge Sorry, I can't track this knowledge to it's source, but found some trail of this opinion in google: link1, link2, link3 –  ThinkJet Jul 4 '13 at 18:28
    
@ThinkJet Maybe it was true a long time ago -- I don't think it has been for quite some time if it ever was. Null numbers take up one byte of space in a row, which is just the marker for the new column, so there's no room in there for the representation of NULL as a number of any form. –  David Aldridge Jul 4 '13 at 20:19

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.