using ansi sql syntax for formatting Numeric

Question

I am using two different databases for my project, Oracle and Apache Derby, and am trying as much as possible to use the ANSI SQL syntax supported by both of the databases.

I have a table with a column amount_paid NUMERIC(26,2).

My old code, which was using Oracle db, needed to retrieve value in this format

SELECT LTRIM(TO_CHAR(amount_paid,'9,999,999,999,999.99'))

How can I convert a numeric value to such a string in the format '9,999,999,999,999.99' using ANSI sql syntax?

Answer 1

I think this is the wrong approach. The format mask is for display purposes, so it really ought to be the concern of the presentation layer. All your data access layer should do is merely execute:

select amount_paid 
from your_table
where ....

This syntax will obviously work whatever database your app attaches to.

Then put the formatting code in the front-end, where it belongs.