Oracle Number Formatting

This may be something you all already know, but it had me scratching my head for a minute. On the one hand, Oracle’s analytic functions like LAST_VALUE make some things kinda cool, but then Oracle smacks me with not handling number masking like pretty much every other language.

Even if you specify a decimal place mask, if the number ends with a 0 in the decimal, it will drop it.

Example:
1) To_Number(44.50,’99.99’) >> 45.5
2) To_Number(100/10.0,’99.99’) >> 10  <<< This one even drops the decimal and returns an integer 10, which leads to a whole different philosophical discussion about the differences between 10 and 10.00.

To get the decimal places mask that we want in Oracle, we have to use To_Char(44.50,’99.99’). But then that value is a string, so you won’t be able to do math on it. Except in ColdFusion, where it’s a numberish string. So to actually format it, we have to remask it in whatever code we use to output the value – ie in ColdFusion = NumberFormat(44.5,’99.90’) >> 44.50 and NumberFormat(100/10.0,’99.00’) >> 10.00

Add this to ColdFusion’s method of determining how to parse a value into JSON, and it makes things super fun. :-/

Moral of my story: If you need a number to be in a specific  format, don’t assume the database will give it back to you the way you thought you told it to.