CODEWITHSUNDEEP
oracle-databaseoracle10gtype-conversion
ipavlic
ipavlic

Reputation: 4966

Oracle TO_NUMBER conversion fails when only fractional part is present

I have a VARCHAR2 with a number, written as a radix point and fractional part e.g. .02. When using

TO_NUMBER(NUMBER_COLUMN)

I get an error

ORA-01722: invalid number

indicating that the format is not recognized. Is there a built-in workaround (I could DECODE or SUBSTR to provide the needed zero?

Upvotes: 2

Views: 7328

Answers (2)

Dmitry Osinovskiy
Dmitry Osinovskiy

Reputation: 10118

I don't have an Oracle instance to try, but maybe using full version of TO_NUMBER would help you: TO_NUMBER(column, '9999999999D99') or TO_NUMBER(column, 'B9999999999D99')

Read more here http://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements004.htm

Also if you need to use different decimal separator then you can specify third parameter to TO_NUMBER. For example, TO_NUMBER('$94 567,00', 'L999G999D00', NLS_NUMERIC_CHARACTERS ', ').

Upvotes: 3

Sathyajith Bhat
Sathyajith Bhat

Reputation: 21851

You're running into this error not because of the radixpoint + fractional part present in your varchar column, rather because your varchar column contains a non-numeric character(s) present which cannot be converted into numbers when to_number runs.

I can demonstrate this via this SQL fiddle

It could also be that your NLS parameters differ from what Oracle expects as a decimal separator, hence throwing this error.

You can change the character Oracle expects as a decimal operator by changing NLS_NUMERIC_CHARACTERS parameter.

ALTER SESSION SET NLS_NUMERIC_CHARACTERS = ".,"

Will tell Oracle to expect a . as a decimal point and , as the thousands separator

Upvotes: 4

Related Questions