Difficulty implementing REGEXP_SUBSTR

Question

Hello all,

I'm running SQL queries on Oracle 10g. I have two tables ( sample data provided below ). i'm trying to extract some fields from Table t2 and update the empty columns in table t1. I'm encountering the following error:

ORA 01722: Invalid Number ( pointing to *REGEXP_SUBSTR* )

I understand this is because of non - numeric data ( like " NO code {...} " ) in my table, that I'm trying to extract using the REGEXP_SUBSTR* Expression I was wondering if someone can suggest me some alternative implementations to help me "copy the entire string" instead of throwing an exception.

MERGE
INTO    temptab t1
USING   directory_list t2
ON      (REGEXP_SUBSTR(codelist, '[^.]+', 1) = t2.tcode)
WHEN MATCHED THEN
UPDATE
SET    t1.tcode = t2.tcode,
       t1.des   = t2.des

temptab t1

Codelist          | T1.tcode   | T1.des
1111.1.803.12.X.Z 
1000.2.3232.145.M.P        
300.12.2982.45.X.Y         
NO code {...}  
1111.1.803.12.X.Z

directory_list t2
    tcode              |   DES
    1000           | powervalue100
    300                | powermax300
    20                 | powermin20
    NO code {...}      | maxvalue plus
    1000           | powervalue100

Thanks,

Novice

Answer 1

You could use CASE to detect when there's no "." in codelist.

MERGE
INTO    temptab t1
USING   directory_list t2
ON      (CASE WHEN INSTR(codelist,'.') = 0 THEN codelist
              ELSE REGEXP_SUBSTR(codelist, '[^.]+', 1)
         END = t2.tcode)
WHEN MATCHED THEN
UPDATE
SET    t1.tcode = t2.tcode,
       t1.des   = t2.des