'Convert Integers to Dollar Format in Oracle Database

I'm using Oracle Database version 12.1.0.2.0. I'm running a query that returns integers that is actually a dollar amount:

SELECT value AS "Valuation" FROM estimates;

Result:

enter image description here

Instead, I would like to show:

$115,508
$38,150
$92,832
$116,222

How can I convert the integers into dollar $ format?

sqloracle


Solution 1:[1]

Use the TO_CHAR function:

TO_CHAR(SOME_NUMBER, '$999,999,999,999,999')

db<>fiddle here

Solution 2:[2]

Use to_char with extended parameters format and nlsparam: TO_CHAR (number) and Number Format Models:

You can specify currency with NLS_CURRENCY and "Group symbol" (NLS_NUMERIC_CHARACTERS('dg'))

SELECT 
   to_char(
       value
      ,'L999g999'
      ,q'[
          NLS_NUMERIC_CHARACTERS = '.,'
          NLS_CURRENCY = '$'
      ]') AS "Valuation" 
FROM estimates;

DBFiddle

Results:

         Valuation
           $11,234
              $104
          $321,349
            $2,837

NB: It's not necessary to specify extra NLS parameters if they correctly set on session level! So it will be much more agile and users will be able to use own session settings.

Solution 3:[3]

Can get rid of the left spaces if you really want that:

SQL> SELECT ltrim(to_char(value,'$999,999')) AS "Valuation" FROM estimates;

Valuation
---------
$115,508
$38,150
$92,832
$116,222

Bobby

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Bob Jarvis - Слава Україні
Solution 2 astentx
Solution 3 Bobby Durrett

Related Questions