SQL & PL/SQL

Hi everyone,

I am using oracle version as follows:

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for 32-bit Windows: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

------------------------------------------------------------------------

I am using regular expression to replace invalid values from a table.

I received oracle error stating "ORA-01722 invalid number"

My query looks like this:

SELECT DISTINCT

MRC_KEY,

PURPOSE_CD,

RESIDENCE_DESC,

to_number(regexp_replace(ICAP_GEN_MADAPTIVE,'[+. ]?0?0?(\d+)[-.]?','\1')) as ICAP_GEN_MADAPTIVE,

From

MRRC_INT

I am not sure what are the invalid values in the table so I can write regexp accordingly.

Any guidance is highly appreciated!

Thanks in advance

J