NLS_NUMERIC_CHARACTERS Dynamic Number Format-CodePudding

I am trying to covert an amount's decimal point to a comma. I used the query below:

select  tax_amt
    ,   to_char(tax_amt, '999999999999999999999999999999999999999990D00', 'NLS_NUMERIC_CHARACTERS='', ''') comma
from    (
select  9.99 tax_amt from dual union all
select  99.99 tax_amt from dual union all    
select  999.99 tax_amt from dual union all
select  9999.99 tax_amt from dual union all
select  99999.99 tax_amt from dual union all
select  999999.99 tax_amt from dual union all
select  9999999.99 tax_amt from dual union all
select  99999999.99 tax_amt from dual union all
select  999999999.99 tax_amt from dual union all
select  9999999999.99 tax_amt from dual union all
select  99999999999.99 tax_amt from dual)

and the results seem to be correct:

tax_amt         zzz
--------        ----------
9.99            9,99
99.99           99,99
999.99          999,99
9999.99         9999,99
99999.99        99999,99
999999.99       999999,99
9999999.99      9999999,99
99999999.99     99999999,99
999999999.99    999999999,99
9999999999.99   9999999999,99
99999999999.99  99999999999,99

However, would it be possible to make the format 999999999999999999999999999999999999999990D00 dynamic?

CodePudding user response：

An Oracle number (assuming that is what tax_amt is) can only have 38 digits of precision. Practically, it seems highly unlikely that you really need to make this dynamic-- the US federal budget is a bit under $5 trillion a year. If your tax_amt was enough to fund the entire US government for an entire year, it would only have 12 digits before the decimal point. If you build a format mask with, say, 15 digits before the decimal point, that gives you space to handle the US budget for the next century or so depending on inflation. That seems more than adequate.

Technically though, sure. You could run a separate query to get the maximum value in the tax_amt column and then create a format string by lpad-ing "9"'s out to whatever the length of that value is. That doesn't seem particularly worthwhile but you could do it.

CodePudding user response：

You can use RPAD() function such like

TO_NUMBER(tax_amt, 
          RPAD(REGEXP_SUBSTR(tax_amt,'[^ .]'),LENGTH(tax_amt),'9')||'0D00', 
         'NLS_NUMERIC_CHARACTERS=''.,''') AS money

or as in your case directly by using TO_CHAR() conversion

TO_CHAR(tax_amt, 
        RPAD(REGEXP_SUBSTR(tax_amt,'[^ .]'),LENGTH(tax_amt),'9')||'0D00', 
       'NLS_NUMERIC_CHARACTERS='', ''') AS comma

Demo