Home » SQL & PL/SQL » SQL & PL/SQL » Comma Separated Number (10 g)
Comma Separated Number [message #365216] Thu, 11 December 2008 02:33 Go to next message
maheshmhs
Messages: 93
Registered: October 2008
Location: Nepal
Member
Hi,

In a table i need to have the number written in comma separated form. For eg i want to have the number 1940909 AS 19,40,909. But this has to be a number format. Is there anyway i could to this?

thank you Shocked
Re: Comma Separated Number [message #365219 is a reply to message #365216] Thu, 11 December 2008 02:36 Go to previous messageGo to next message
dr.s.raghunathan
Messages: 540
Registered: February 2008
Senior Member
yes format 99g99g999d00
Re: Comma Separated Number [message #365220 is a reply to message #365216] Thu, 11 December 2008 02:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> select to_char(1940909, '99g99g990') from dual;
TO_CHAR(19
----------
 19,40,909

1 row selected.

Regards
Michel

Ooops, dr.s.raghunathan beats me by 2 minutes.

[Updated on: Thu, 11 December 2008 02:39]

Report message to a moderator

Re: Comma Separated Number [message #365226 is a reply to message #365220] Thu, 11 December 2008 02:57 Go to previous messageGo to next message
maheshmhs
Messages: 93
Registered: October 2008
Location: Nepal
Member
Thanks a lot.

I still have one more question. The above example works if i am working for individual number. But if i am working on all rows of a column, i don't know the number of digits in number of that column. In that case it will the result in ######## format for those that don't match the number of digits.
Is there any way round?
Re: Comma Separated Number [message #365229 is a reply to message #365226] Thu, 11 December 2008 03:01 Go to previous messageGo to next message
dr.s.raghunathan
Messages: 540
Registered: February 2008
Senior Member
at the time of creation of table, you would have mentioned the precision / length isn't it?
or i am not able to visualise your doubt?
yours
dr.s.raghuanthan

Re: Comma Separated Number [message #365230 is a reply to message #365226] Thu, 11 December 2008 03:02 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You need to specify a format mask that is long enough for the largest number that you're going to apply the TO_CHAR to.
Re: Comma Separated Number [message #365240 is a reply to message #365230] Thu, 11 December 2008 03:42 Go to previous messageGo to next message
maheshmhs
Messages: 93
Registered: October 2008
Location: Nepal
Member
thank you guys, i used a very long format '99g99g99g99g99g99g999'
this works for me.
Re: Comma Separated Number [message #365294 is a reply to message #365240] Thu, 11 December 2008 05:48 Go to previous messageGo to next message
mamalik
Messages: 266
Registered: November 2008
Location: Pakistan
Senior Member

DEAR Michle Uncle

What is difference between to_char(1940909, '99g99g990') and to_char(1940909, '99,99,990') ???

Best Regards.
Muhammad Asif Malik.

[Updated on: Thu, 11 December 2008 05:49]

Report message to a moderator

Re: Comma Separated Number [message #365300 is a reply to message #365294] Thu, 11 December 2008 05:55 Go to previous message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> alter session set nls_territory=france;

Session altered.

SQL> select to_char(1940909, '99g99g990'), to_char(1940909, '99,99,990') from dual;
TO_CHAR(19 TO_CHAR(19
---------- ----------
 19 40 909  19,40,909

1 row selected.

Regards
Michel
Previous Topic: Row Source Generator
Next Topic: Query (merged 3)
Goto Forum:
  


Current Time: Mon Dec 05 06:28:28 CST 2016

Total time taken to generate the page: 0.08634 seconds