OraFAQ Forum: SQL & PL/SQL » number to char convertion

Home » SQL & PL/SQL » SQL & PL/SQL » number to char convertion (oracle 10g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

number to char convertion [message #629242]

Fri, 05 December 2014 00:23

vivek_rol
Messages: 65
Registered: February 2009

Member

how to convert 259786.09 into 2,59,786.09 ?

Report message to a moderator

Re: number to char convertion [message #629243 is a reply to message #629242]

Fri, 05 December 2014 00:28

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

vivek_rol wrote on Thu, 04 December 2014 22:23

how to convert 259786.09 into 2,59,786.09 ?

use TO_CHAR()

Report message to a moderator

Re: number to char convertion [message #629244 is a reply to message #629242]

Fri, 05 December 2014 00:42

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

You can set the DECIMAL and GROUP separator to get the desired output. And use TO_CHAR as already suggested by BlackSwan.

SQL> alter session set nls_numeric_characters='.,';

Session altered.

SQL> select to_char(259786.09, '9G99G999D99') num from dual
  2  /

NUM
------------
 2,59,786.09

SQL>

where,
G = Group separator
D = Decimal separator

you can also check the NLS_TERRITORY for the same.

Regards,
Lalit

Report message to a moderator

Re: number to char convertion [message #629245 is a reply to message #629244]

Fri, 05 December 2014 00:52

Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Please do NOT give complete solution to basic question that can DIRECTLY addressed by the documentation.
A link to format model would be sufficient and far better.

Report message to a moderator

Re: number to char convertion [message #629254 is a reply to message #629245]

Fri, 05 December 2014 01:35

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

Point taken. However, I gave one way of doing it, OP can research further about NLS_TERRITORY.

Report message to a moderator

Re: number to char convertion [message #629257 is a reply to message #629254]

Fri, 05 December 2014 01:49

Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Precisely! if you'd given the link to the documentation OP would see the dependencies and might investigate it and other formats, something your post prevent as he gives a solution OP would be satisfied with.

Report message to a moderator

Re: number to char convertion [message #629280 is a reply to message #629242]

Fri, 05 December 2014 08:30

joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA

Senior Member

vivek_rol wrote on Fri, 05 December 2014 01:23

2,59,786.09 ?

What kind of number is that?

Report message to a moderator

Re: number to char convertion [message #629282 is a reply to message #629280]

Fri, 05 December 2014 08:48

pablolee
Messages: 2882
Registered: May 2007
Location: Scotland

Senior Member

Quote:

What kind of number is that?

Lakhs and crores? (guess)

Report message to a moderator

Re: number to char convertion [message #629283 is a reply to message #629282]

Fri, 05 December 2014 09:14

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

In India it would be two lakhs fifty nine thousand seven hundred and eighty six(excluding the decimal value).

Edit : Modified the position of "and", as it should come at last.

[Updated on: Sat, 06 December 2014 00:25]

Report message to a moderator

Previous Topic:	ORA-06553: PLS-306: wrong number or types of arguments in call to 'ROWID_BLOCK_NUMBER'
Next Topic:	UNPIVOT mutiple columns with different conditions

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 26 19:17:26 CDT 2024