0

There is a number column in database. When it is fetched as char using TO_CHAR then the digit before decimal point is lost.

Select TO_CHAR(column) from table

For e.g. 0.5 is stored in the column but when selected it returns .5

Similarly if -0.5 is stored then -.5 is returned .

What could be the reasons for this ?

Is it related to NLS setting ?If yes which setting should be changed to retrieve the value as it is stored .

William Robertson
  • 15,273
  • 4
  • 38
  • 44
PTK
  • 307
  • 4
  • 19
  • 2
    Leading zeros are not stored in the database. Numbers are stored using internal formats. If you want the leading zero, then use `to_char(col, '0.0')`. – Gordon Linoff Aug 20 '21 at 16:20
  • @PTK storage and display are two different things. Use the `dump()` function to see how it's really stored. It's not `'-0.5'`. – William Robertson Aug 20 '21 at 18:41

1 Answers1

3

You can do something like this:

select to_char(.5,'0.000000')from dual

That's not an NLS issue. NLS changed things like decimal separator, thousands separator, etc. Not sure how many decimals needed after the decimal point, so you can change that. I would also look at the number format models doc

OldProgrammer
  • 12,050
  • 4
  • 24
  • 45