Howto return multiple values with regexp =~ splitpath oneline

[j0nxuser]

I have several tables in my 10G database that are fed to me from a perl script that fetches XML data from another company (data is not stored as XML - plain old tables and fields). The perl script databases all of the data in different tables - all of which work fine. My challenge stems from a field called "REFERENCENUMBERVALUE". This field in my table is actually three separate values concatenated together, within the XML feed.

Sample piece of data:
'CPE230325305ABA;76204845LBADTN0738AC801'

Broken apart, the three values are
1) 'CPE230325305ABA'
1a) the ';' may or may not be there
2) '76204845'
3) 'LBADTN0738AC801'

After row inserts into the table I would like to create a trigger that will use Oracle regexp to rip the three values out of this field and insert the separate values in new columns within the same table.

So far I have this working, but this only returns one of the three values:

select regexp_substr('CPE230325305ABA;76204845LBADTN0738AC801','^([a-zA-Z]{3}[0-9]{9})([a-zA-Z]{3})?')from dual;

Returns:
CPE230325305ABA


Another attempt with the regexp:
^([a-z]{3}[0-9]{9})([a-z]{3})?(?:?([0-9]{8})(.*)

Returns:
null


What do I need to do on my select statement to get this to work?

I know in perl I can do a split-path one liner, but do not know the equivalent in Oracle

Thank you!
Mark

 

[jaxtell]

I'm not sure you can do it in one line. Here is it working in 3 lines...

         select regexp_substr('CPE230325305ABA;76204845LBADTN0738AC801','^([a-zA-Z]{3}[0-9]{9})([a-zA-Z]{3})?')from dual; --result1
         
         select regexp_substr('CPE230325305ABA;76204845LBADTN0738AC801','\d{8}',1,2) from dual; --result2
         
         select substr('CPE230325305ABA;76204845LBADTN0738AC801',length(result1 || result2)+1) from dual; --result3

 

[j0nxuser]

OK - That's what I was afraid of. I was thinking three lines. Thank you!

Mark