Formato de numeros con moneda en Oracle

 Oracle Database provides the TO_CHAR(number) function that enables you to format numbers based on a given format model. There are three format elements that you can use with the TO_CHAR() function in order to return a value as a currency.

The currency format elements are L, C, and U.

C Returns the ISO currency symbol (the current value of the NLS_ISO_CURRENCY parameter).

L Returns the local currency symbol (the current value of the NLS_CURRENCY parameter).

U Returns the Euro (or other) dual currency symbol, determined by the current value of the NLS_DUAL_CURRENCY parameter.

These format models allow you to output the currency sign based on your currency/region settings, rather than having to provide a fixed currency symbol.

While it’s true that you can provide a string literal for the currency (for example, a dollar sign ($) for dollars), this assumes that the currency is denominated in that hardcoded value. There are many other possible currencies around the world, and the format elements are able to dynamically return the local currency symbol for the user’s session.

Example

Here’s an example to demonstrate:

ALTER SESSION SET NLS_TERRITORY = 'Australia';

SELECT 

    TO_CHAR(12345, 'fmL99G999D00') AS "r1",

    TO_CHAR(12345, 'fmC99G999D00') AS "r3",

    TO_CHAR(12345, 'fmU99G999D00') AS "r3"

FROM DUAL;

Result:

           r1              r3            r3 

_____________ _______________ _____________ 

$12,345.00    AUD12,345.00    $12,345.00  

The only difference between these three columns is the currency format element. The first uses L, the second uses C, and the third uses U.

They all use the fm format modifier to suppress any padding. They also use the 9 and 0 format elements to output the numbers (the 0 element includes leading/trailing zeros). They also include a group separator (represented by G), a decimal character (represented by D).

Dual Currencies

In the above example, I set the NLS_TERRITORY to Australia. This resulted in the same currency symbol being returned in two of the three columns (i.e. the r1 and r3 columns).

In the following example, I use a different territory:

ALTER SESSION SET NLS_TERRITORY = 'Denmark';

SELECT 

    TO_CHAR(12345, 'fmL99G999D00') AS "r1",

    TO_CHAR(12345, 'fmC99G999D00') AS "r3",

    TO_CHAR(12345, 'fmU99G999D00') AS "r3"

FROM DUAL;

Result:

           r1              r3            r3 

______________ _______________ _____________ 

kr12.345,00    DKK12.345,00    €12.345,00   

This time we get a different currency symbol for each of the r1 and r3 columns.

See How to Check the Values of the NLS Parameters (https://database.guide/how-to-check-the-values-of-the-nls-parameters-in-oracle-database/)

if you need to check them.

The 'nlsparam' Argument

When using the TO_CHAR() function, the 'nlsparam' argument can be used to specify the decimal character and the group separator, the local currency symbol, and the international currency symbol.

It takes the following form:

'NLS_NUMERIC_CHARACTERS = ''dg''

   NLS_CURRENCY = ''text''

   NLS_ISO_CURRENCY = territory '

Example:

SELECT 

    TO_CHAR(

        1234.56, 

        'fmL99G999D99',

        'NLS_NUMERIC_CHARACTERS = '',.''

        NLS_CURRENCY = ''€''

        NLS_ISO_CURRENCY = Germany'

    )

FROM DUAL;

Result:

€1.234,56

Here it is again, but this time I replace L with C in the format model:

SELECT 

    TO_CHAR(

        1234.56, 

        'fmC99G999D99',

        'NLS_NUMERIC_CHARACTERS = '',.''

        NLS_CURRENCY = ''€''

        NLS_ISO_CURRENCY = Germany'

    )

FROM DUAL;

Result:

EUR1.234,56

The C returns the ISO currency symbol, which in this case is EUR.

DE la segunda paguina de las fuebtes: 

select .5 as colm from dual;   gives 0.5 as the output

But  select .5||'$' as colm from dual;   gives .5$ as the result. 

Why I am not getting 0.5$

How can I achieve 0.5$ in the second query?

respuetas

Instead of appending, we can do like below. Format the currency in expected format.

select TO_CHAR(.5,'FM999999990.099999999$') as colm from dual;

Otherwise, you still can concat the currency symbol like,

select TO_CHAR(.5,'FM999999990.099999999')||'$' as colm from dual;

Key is you have to explicitly mention the number format before concatenating.

select TO_CHAR(5,'FM0.99999999')||'$' as new_col from dual It gives me just 5.$

Oracle Setup:

CREATE TABLE your_table ( value ) AS

  SELECT 0.5 FROM DUAL UNION ALL

  SELECT 5   FROM DUAL;

Query:

SELECT value,

       REGEXP_REPLACE(

         TO_CHAR( value, 'FM9999999990.99' ),

         '\.$'

       ) || '$' AS formatted_value,

       CASE

         WHEN TRUNC( value ) = value

         THEN TO_CHAR( value, '9999999990' )

         ELSE TO_CHAR( value, 'FM9999999990.99' )

       END || '$' AS alternate_value,

       TO_CHAR( value, '9999999990.99L' ) AS currency_value

FROM   your_table;

Output:

VALUE FORMATTED_VALUE ALTERNATE_VALUE CURRENCY_VALUE

----- --------------- --------------- --------------

  0.5 0.5$            0.5$            0.50$

    5 5$              5$              5.00$

Fuentes.

Artículo:   "How to Format a Number as Currency in Oracle" Publicado en https://database.guide por Ian el September 10, 2021. Consultado el 30/06/2024.

URL: https://database.guide/how-to-format-a-number-as-currency-in-oracle/

Artículo:   "Decimal numbers and currency formatting in Oracle" Publicado en https://stackoverflow.com/ el 14-03-2017. Consultado el 30/06/2024.

URL: https://stackoverflow.com/questions/42782180/decimal-numbers-and-currency-formatting-in-oracle