CODEWITHSUNDEEP
sqloracle-databaseformat
Mira7
Mira7

Reputation: 25

How to add zero on the beginning of the double number

I am trying to solve the following problem:

A double number, that is a result of dividing 2 double numbers, has on the beginning 0 and this is not displayed. (E.g. I would expect 0.537 but I can see only .0537) This is quite problem because the number is then converted to string and I need this zero on the beginning.

For the better imagination, please see the part of the code (which does not work for these numbers):

...

ELSE REPLACE(TRIM(to_char(ROUND(ab.price, 5)/ABS(ROUND(ab.position,5)), '999999.99999')),'.',',') END as Price
...

result: ,57300

expected result: 0,57300

Thank you for the answers.

M.

Upvotes: 0

Views: 204

Answers (1)

Thorsten Kettner
Thorsten Kettner

Reputation: 94914

You are using the wrong format. 9 is for an optional digit. Use 0 for an obligatory one. You get the decimal separator matching your regional settings with D:

TO_CHAR( ROUND(ab.price, 5)/ABS(ROUND(ab.position, 5), '999990D99999' )

If you want the comma to be the decimal separator regardless of your settings, then specify this with NLS_NUMERIC_CHARACTERS:

TO_CHAR(
  ROUND(ab.price, 5)/ABS(ROUND(ab.position, 5), 
  '999990D99999', 
  'NLS_NUMERIC_CHARACTERS='',.''' )

Upvotes: 2

Related Questions