Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Making large numbers readable using comma

CastellNov 13 2017 — edited Nov 13 2017

DB version : 11.2.0.4, 12.2

For reporting purposes, I would like to use a comma for large numbers.

I think the norm in the English speaking world is to place a comma after every third digit from RIGHT to LEFT for numbers which has more than three digits

Any idea about the number format I should use ? As you can see in my below attempts, I have to use custom format for each large number. Is there a way I can achieve this using a single format ?

-- Trying to print 7,438,000 (Seven million four hundred and thirty eight thousand)

SQL> select to_char(7438000,'9,999') from dual;

TO_CHA

------

######

SQL> select to_char(7438000,'9,999,999') from dual;

TO_CHAR(74

----------

7,438,000

-- Trying to print 5,393,664,534

-- ie. Five billion three hundred and ninty three million ... with a comma after every third digit from right to left.

DB version : 11.2.0.4, 12.2

For reporting purposes, I would like to use a comma for large numbers.

I think the norm in the English speaking world is to place a comma after every third digit from RIGHT to LEFT for numbers which has more than three digits

Any idea about the number format I should use ? As you can see in my below attempts, I have to use custom format for each large number. Is there a way I can achieve this using a single format ?

-- Trying to print 7,438,000 (Seven million four hundred and thirty eight thousand)

SQL> select to_char(7438000,'9,999') from dual;

TO_CHA

------

######

SQL> select to_char(7438000,'9,999,999') from dual;

TO_CHAR(74

----------

7,438,000

-- Trying to print 5,393,664,534

-- ie. Five billion three hundred and ninety three million ... with a comma after every third digit from right to left.

SQL> select to_char(5393664534,'9,999,999') from dual ;

TO_CHAR(53

----------

##########

SQL>

Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Dec 11 2017
Added on Nov 13 2017
4 comments
5,528 views