Formatting an SQL numeric query result with an arbitrary number of decimal places

Question

I have a database table with these two columns:

• Amount: numeric (18,0)
• DecimalPlaces: numeric (18,0)

This table can store amounts in various currencies, with the decimal place removed from the amount (I can't change this data model). For example, there might be two rows like this:

• 1290, 2 (This is £12.90, needs to appear as "12.90")
• 3400, 0 (This is 3400 Japanese Yen, needs to appear as "3400")

I need an SQL query for both Oracle and SQL Server that will format each amount with the correct number of decimal places, preserving any trailing zeroes as illustrated above. I can't use stored procedures, a reporting tool, or Excel.

Answer 1

The best I've been able to come up with so far is:

select Amount/power(10, DecimalPlaces) from MyTable

But it doesn't do exactly what I want:

• Oracle: the trailing zeroes are stripped, so US$15.00 looks like "15", not "15.00"
• SQL Server: a whole lot of extra trailing zeroes are added, so $23.99 looks like "23.99000000000" instead of "23.99"

Answer 2

How about?

select 12345 amount, 2 decimalPlaces, substr( to_char( 12345 ), 1, length (to_char( 12345 ) ) - 2 ) || '.' || substr( to_char( 12345 ), -2 ) result from dual /

     amount decimalPlaces result
 ---------- ------------- ------
     12345              2 123.45

Answer 3

Martlark's answer for Oracle led me to this solution for SQL Server:

select
  left(cast(Amount as varchar), len(cast(Amount as varchar)) - DecimalPlaces) +
  left('.', DecimalPlaces) +
  right(cast(OriginalCurrencyAmount as varchar), DecimalPlaces
) as FormattedAmount
from MyTable

Answer 4

This is gross but worked for the current inputs on SQL server.

select 
    substring(
     CAST(
      CAST(
        (amount *  power(-0.100000000000000000,decimalPlaces*1.000000000000000000)) as numeric(36,18)
      )as varchar(30)
     )
    ,1,len(cast(amount as varchar(20))) + (CASE WHEN decimalPlaces = 0 THEN 0 ELSE 1 END )) 

from
 myTable

Answer 5

Your problem is that there isn't an easy way to do this for both SQLServer and Oracle in one query.

The Correct way to do this for SQLServer is to use STR:

Select STR(Amount, 18, DecimalPlaces) from myTable;

The correct way to do this for Oracle is using to_char:

SELECT to_char (amount, '99999999999999.'||rpad('',DecimalPlaces, '0')) 
from MyTable;

The queries presented by jms and Andrew won't work in an Oracle query because Oracle SQL uses LENGTH() not LEN(). And Oracle uses to_char() not Cast().

Answer 6

In SQL server you can :

select stuff(convert(varchar,amount) ,
         len(convert(varchar,amount)) - DecimalPlaces - 1, 0, ".")