We help IT Professionals succeed at work.

How to format query result: Number and Currency

winnie_zhang
winnie_zhang asked
on
Hi,

I know to_char is able to format the 'date' datatype for the result.  Will it also be able to format 'number' and 'currency' datatypes?  To be more specific, I want the 'number' to be shown as
 ?9999.99?, and the 'currency' as ?$9,999.99??  I found there is a 'Format' function, but don't know how to use it.  Please help.

Thanks,
 
Comment
Watch Question

Nunber :

to_char(number_feild,'99999.99');

Currency :

to_char(currency,'$999,999.99');



I hope it helps, To_Char works with numbers and dates too.

Regards
Usama Munir dar
jtriftsMI and Automation
CERTIFIED EXPERT

Commented:
To use the Oracle default formats for currency you can reference as follows:

TO_CHAR(your_value,$999G999G999D99)

This will place a dollar sign in front and use the default formats fo G (thousands delimiter) and D for decimal delimiter.

Thus for a system using the NLS_LANG setting of AMERICAN_AMERICA.WEISO... you might expect:
$999,999,999.00
while as FINNISH_FINLAND.WEISO...you might expect the same number to be displayed as:
$999.999.999,00 (or possibly $999 999 999,00)

As a personal coding habit, if I am going to format two or more values, I would set up a format variable and initialise it in the procedure declaration:
e.g.

v_cur_format  CONSTANT CHAR (15) := '$999G999G999D99';
v_date_format CONSTANT CHAR (21) := 'DD.MM.YYYY HH24:MI:SS';

...then throughout the code I would format simplay as :

v_formatted_cur := TO_CHAR(your_value,v_cur_format);
v_formatted_date := TO_CHAR(your_date,v_date_format);

This way, if your currency and date format requirements change in the future, you'll only have to make a change to the variable declaration (i.e. change only in one place) rather than having to change every instance where you have formatted a field.

HTH,

JT
Please update and finalize this old, open question. Please:

1) Award points ... if you need Moderator assistance to split points, comment here with details please or advise us in Community Support with a zero point question and this question link.
2) Ask us to delete it if it has no value to you or others
3) Ask for a refund so that we can move it to our PAQ at zero points if it did not help you but may help others.

EXPERT INPUT WITH CLOSING RECOMMENDATIONS IS APPRECIATED IF ASKER DOES NOT RESPOND.

Thanks,

** Mindphaser - Community Support Moderator **
Force accepted

** Mindphaser - Community Support Moderator **

Explore More ContentExplore courses, solutions, and other research materials related to this topic.