CODEWITHSUNDEEP
sqloracle-database
Aaron Jevons
Aaron Jevons

Reputation: 13

Oracle Number Issue

Good Day,

I have an issue I could use your help with.

A customer would like invoice amounts displayed as follows:

Example Invoice Amounts 

-405.12 to be shown as 000000040512

&

-400.00 to be shown as 000000040000

The following query works fine for the 405.12 amount, but for the 400.00 amount it drops the two zeros on the right side

LPAD(REPLACE((invoiceamt*-1),'.',''),12,0)

How may I solve this issue?

Thank You Aaron

Upvotes: 1

Views: 65

Answers (3)

Alex Poole
Alex Poole

Reputation: 191580

Another question has reminded me of the existence of the V format model element:

Returns a value multiplied by 10^n (and if necessary, round it up), where n is the number of 9's after the V.

But you can use zeros instead of 9s to keep leading zeros, and add FM to remove the leading space (which is there for a - symbol for negative values - which you won't have). So you can also do:

with t (n) as (
  select -405.12 from dual
  union all select -400 from dual
)
select n, to_char(abs(n), 'FM0000000000V00') as result
from t;

         N RESULT       
---------- -------------
   -405.12 000000040512 
      -400 000000040000

Upvotes: 0

Dmitrii Bychenko
Dmitrii Bychenko

Reputation: 186843

I suggest

 to_Char(abs(invoiceamt) * 100, '000000000000')

where

    abs  - absolute value - get rid of sign (-)
  * 100  - removing decimal point
to_Char  - final formatting (12 mandatory digits)

Upvotes: 7

OTTA
OTTA

Reputation: 1081

Forget the REPLACE, just multiply invoiceamt by -100 and then LPAD to the required length.

Upvotes: 2

Related Questions