CODEWITHSUNDEEP
sqloracle-database
Dead Programmer
Dead Programmer

Reputation: 12575

oracle sql query to format number by comma

Guys i want to print the following data with comma separated values using oracle sql

696585242087


to 

69,658,524,2087

and same for the decimal.

Upvotes: 26

Views: 116515

Answers (4)

OscarSosa
OscarSosa

Reputation: 89

Taking the Dead programmer's update (24/Jan/2011) (original author of the question) and the comment of Amir Pashazadeh (5/Mar/2016), we can get the desired comma separated format with the following example.

This code applies for numbers with equal or less than 15 digits, if you will need for bigger length, you should add more 999,999 according with the max of length you will manage.

SELECT TRIM(TO_CHAR(12345678, '999,999,999,999,999')) ttry FROM DUAL
UNION ALL
SELECT TRIM(TO_CHAR(    1234, '999,999,999,999,999')) ttry FROM DUAL
;

Result:

enter image description here

Upvotes: 2

Herb
Herb

Reputation: 743

See the Oracle docs for all the insanity that can be done for number formatting. The short version is you need the "TO_CHAR" function, and provide a formatting string for the output:

TO_CHAR( col_name, '999,999,999,999')

Should do what you need.

Upvotes: 29

Quassnoi
Quassnoi

Reputation: 425251

SELECT  TO_CHAR(696585242087, '99G999G999G9999', 'NLS_NUMERIC_CHARACTERS=",."')
FROM    dual

Upvotes: 31

neil
neil

Reputation: 76

take a look to to_char: http://www.oradev.com/oracle_number_format.jsp

Upvotes: 2

Related Questions