CODEWITHSUNDEEP
sqlregexoracle-databaseextractcurrency
IdoIT4theMoney
IdoIT4theMoney

Reputation: 3

Regexp_replace WITH DECIMAL is returning error ORA-01722 "Invalid Number"

In Oracle I had this code: 

regexp_replace(DESCRIPTION_FIELD, '[^0-9.]+', '')

that worked fine, but now I'm getting the error:

ORA-01722 "Invalid Number".

Removing the decimal fixes this, but removes the decimal from the returning value. I'm guessing there's some bad data added recently. Solutions?

Upvotes: 0

Views: 2901

Answers (2)

F.Madsen
F.Madsen

Reputation: 702

This error would normally occur if the NLS_NUMERIC_CHARACTERS for your session has a wrong decimal point setting.

You can change the setting for your session using this:

alter session set NLS_NUMERIC_CHARACTERS = '.,';

or

alter session set NLS_NUMERIC_CHARACTERS = ',.';

That will set the decimal and group separator for your current sessionn only.

Or there is a more general solution in your select statement that will always assume '.' as decimal point in your data from the table. And will display the number with the decimalt point for your session.

select to_number(regexp_replace(ESCRIPTION_FIELD, '[^0-9.]+', ''), '999999999999.99999', 'NLS_NUMERIC_CHARACTERS = ''.,''') from <your table>;

EDIT

If more decimal point are found in the string and you can accept that some are skipped then this might work.

select to_number(regexp_substr(regexp_replace(ESCRIPTION_FIELD, '[^0-9.]+', ''),'([0-9]*.[0-9]*)'), '999999999999.99999', 'NLS_NUMERIC_CHARACTERS = ''.,''') from <your table>;

Upvotes: 0

Alex Poole
Alex Poole

Reputation: 191275

The regexp_replace() isn't generating that error; the problem is when you cast the result of that replacement to a number. For example, for the original value XYZ2626...266.88 your pattern brings back 2626...266.88, and to_number('2626...266.88') throws ORA-01722.

by finding numbers with a format of "Some numbers, a decimal, then two numbers"

You could look for that rather than trying to exclude other characters:

with your_table (description_field) as (
  select 'No money value' from dual
  union all
  select 'Some sensible 98765.43 value' from dual
  union all
  select '01234-1234545 54.00' from dual
  union all
  select 'XYZ2626...266.88' from dual
  union all
  select 'ABC-123.45XYZ' from dual
  union all
  select 'ABC123.45XYZ6.78' from dual
)
select description_field,
  regexp_replace(DESCRIPTION_FIELD, '[^0-9.]+', '') as original,
  regexp_replace(DESCRIPTION_FIELD, '.*?((-?\d+\.\d{2})[^0-9]*)?$', '\2') as new
from your_table;

DESCRIPTION_FIELD            ORIGINAL             NEW                 
---------------------------- -------------------- --------------------
No money value                                                        
Some sensible 98765.43 value 98765.43             98765.43            
01234-1234545 54.00          01234123454554.00    54.00               
XYZ2626...266.88             2626...266.88        266.88              
ABC-123.45XYZ                123.45               -123.45             
ABC123.45XYZ6.78             123.456.78           6.78

I've allowed for negative numbers but you might not want those... and if there is more than one potential money values it'll take the last one.

The capturing group (-?\d+\.\d{2}) looks for an optional minus sign, followed by any number of digits, followed by a period, followed by exactly 2 digits. But that on its own wouldn't prevent further digits afterwards, so it's followed by [^0-9]* to make sure that doesn't happen. That combination is enclosed in a second grouping to allow it to be optional (followed by ?) - otherwise values without anything that looks like a money amount are passed through unaltered, which will also error presumably.

Upvotes: 2

Related Questions