Oracle to_number(1234.567)?

Question

I am trying to convert a number of type varchar(e.g 1234.456) in to floating point number using oracle function to_number(). In my PC (Locale German) the Oracle SQLDeveloper returning number in the fromat 1234,567 instead of 1234.567 and inturn it is causing the oracle error ORA-01722-invalid number. I cahnged my system locale to en_usa but no use. How can i change the behavior of oracle ?

Help will be greatly appriciated

Is en_usa a valid locale? I think country codes are 2 char. en_us — John Watts, Oct 09 '12 at 11:41
Try using nlsparam. http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions191.htm — John Watts, Oct 09 '12 at 11:46
Or use a format model with an explicit comma instead of 'D'. — Colin 't Hart, Oct 09 '12 at 11:49
See also http://stackoverflow.com/questions/4142891/how-to-efficiently-convert-text-to-number-in-oracle-pl-sql-with-non-default-nls — Vadzim, Jan 15 '15 at 16:41

A.B.Cade · Accepted Answer · 2012-10-09T13:32:33.240

3

Try it like this:

SELECT to_number('1234.5678', '9999D9999', 'NLS_NUMERIC_CHARACTERS=.,')
FROM dual;

Here is a fiddle

edited Oct 09 '12 at 13:32

answered Oct 09 '12 at 12:37

A.B.Cade

16,735
1
37
53

Thank you very much for your reply. does it worked for you ? for me it did'nt – user964147 Oct 09 '12 at 12:50
@user964147, yes, I added a fiddle so you can see... **Note:** I've put only four digits in my format (`9999`) if you expect more then you should change the format (ie `999999999D99999999`) – A.B.Cade Oct 09 '12 at 13:28

Oracle to_number(1234.567)?

1 Answers1