Search code examples
sqloracle-databasecastingdecimalcurrency

Currency column with no decimals: add in decimal

I've been reviewing a currency column that has no decimal spaces. It's an output from a legacy system loaded into our Oracle database.

If the field has three or more numerals it should have a decimal at three spaces right.

If the value has less than three numerals, it should have a decimal and a leading zero.

For example:

  • 2050 should be converted to 2.050
  • 110 should be converted to .110
  • 50 should be converted to .050

I've tried using cast, but I received the error 'invalid datatype.'

It's a basic select statement:

    select 
    customer_id
    cast(ENDING_BALANCE as (decimal(10,3)) as Bal_1
    from Current_Balances

Any suggestions would be appreciated.

Solution

  • I think you need to cast it to a number and divide by 1000

    SELECT CAST(CAST('2050' as INT)/1000 as DECIMAL(10,3)) FROM DUAL

    If you really mean to have the output format looking like that, you need to TO_CHAR it

    SELECT LTRIM(TO_CHAR(CAST('2050' as INT)/1000, 'FM0.000'), '0') FROM DUAL