OraFAQ Forum: SQL & PL/SQL » String to Number conversion

Home » SQL & PL/SQL » SQL & PL/SQL » String to Number conversion (Oracle 11G)

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

String to Number conversion [message #630743]

Mon, 05 January 2015 01:21

sinpeak
Messages: 59
Registered: January 2011
Location: india

Member

Hello,

This should explain my problem :

SQL> select value from nls_session_parameters where parameter='NLS_NUMERIC_CHARACTERS';

VALUE
--------------------------------------------------------------------------------
,.

SQL> select to_number('12.3456') from dual;
select to_number('12.3456') from dual
ERROR at line 1:
ORA-01722: numero non valido

Am I doing anything wrong ?
Please advise.

Thanks.

Report message to a moderator

Re: String to Number conversion [message #630747 is a reply to message #630743]

Mon, 05 January 2015 01:37

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

Senior Member

SQL> ALTER SESSION SET NLS_NUMERIC_CHARACTERS =',.';

Session altered.

SQL>
SQL> SELECT VALUE FROM nls_session_parameters WHERE parameter='NLS_NUMERIC_CHARACTERS';

VALUE
----------------------------------------------------------------
,.

SQL> SELECT to_number('12.3456') FROM dual;
SELECT to_number('12.3456') FROM dual
                 *
ERROR at line 1:
ORA-01722: invalid number


SQL>
SQL> ALTER SESSION SET NLS_NUMERIC_CHARACTERS ='.,';

Session altered.

SQL>
SQL> SELECT VALUE FROM nls_session_parameters WHERE parameter='NLS_NUMERIC_CHARACTERS';

VALUE
----------------------------------------------------------------
.,

SQL> SELECT to_number('12.3456') FROM dual;

TO_NUMBER('12.3456')
--------------------
             12.3456

Or, in your case, without altering session -

SQL> SELECT VALUE FROM nls_session_parameters WHERE parameter='NLS_NUMERIC_CHARACTERS';

VALUE
----------------------------------------------------------------
,.

SQL> SELECT to_number('12,3456') FROM dual;

TO_NUMBER('12,3456')
--------------------
             12,3456

Regards,
Lalit

[Updated on: Mon, 05 January 2015 02:00] by Moderator

Report message to a moderator

Re: String to Number conversion [message #630751 is a reply to message #630743]

Mon, 05 January 2015 02:04

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

Senior Member
Account Moderator

You can also specify the format:

SQL> select to_number('12.3456') from dual;
select to_number('12.3456') from dual
                 *
ERROR at line 1:
ORA-01722: invalid number


SQL> select to_number('12.3456','9999999.9999999') from dual;
TO_NUMBER('12.3456','9999999.9999999')
--------------------------------------
                               12,3456

1 row selected.

Report message to a moderator

Re: String to Number conversion [message #630789 is a reply to message #630751]

Mon, 05 January 2015 07:11

Maaher
Messages: 7065
Registered: December 2001

Senior Member

And, as a bonus, you could use the third parameter of TO_NUMBER:

SQL> select to_number('12.3456', '9999999D9999999', 'nls_numeric_characters=.,') xx
  2  from dual
  3  /

        XX
----------
   12,3456

Be sure to use the "D" in the format mask though.

MHE

Report message to a moderator

Previous Topic:	Text based search
Next Topic:	Update statement updates all rows

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Apr 23 04:51:12 CDT 2024