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:

Instead, I would like to show:

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

How can I convert the integers into dollar $ format?

CodePudding user response：

Use the TO_CHAR function:

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

db<>fiddle here

CodePudding user response：

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.

CodePudding user response：

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