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.
-
3Why would you want a comma before the right most 4 digits?MatBailie– MatBailie2011-01-24 15:34:46 +00:00Commented Jan 24, 2011 at 15:34
Add a comment
|
4 Answers
SELECT TO_CHAR(696585242087, '99G999G999G9999', 'NLS_NUMERIC_CHARACTERS=",."')
FROM dual
2 Comments
Conrad Frix
+1 Allows for countries that use a decimal comma
Amir Pashazadeh
What if I don't know how many digits the source number have, I mean is there any pattern to group every 3 digits regardless of number's length?
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.
Comments
take a look to to_char: http://www.oradev.com/oracle_number_format.jsp
Comments
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:
