Oracle REGEXP_SUBSTR to ignore the first ocurrence of a character but include the 2nd occurence

Question

I have a string that has this format "number - name" I'm using REGEXP_SUBSTR to split it in two separate columns one for name and one for number.

SELECT 
REGEXP_SUBSTR('123 - ABC','[^-]+',1,1) AS NUM,
REGEXP_SUBSTR('123 - ABC','[^-]+',1,2) AS NAME
from dual;

But it doesn't work if the name includes a hyphen for example: ABC-Corp then the name is shown only like 'ABC' instead of 'ABC-Corp'. How can I get a regex exp to ignore everything before the first hypen and include everything after it?

Answer 1

You want to split the string on the first occurence of ' - '. It is a simple enough task to be efficiently performed by string functions rather than regexes:

select 
    substr(mycol, 1, instr(mycol, ' - ') - 1) num,
    substr(mycol, instr(mycol, ' - ') + 3) name
from mytable

Demo on DB Fiddlde:

with mytable as (
    select '123 - ABC' mycol from dual 
    union all select '123 - ABC - Corp' from dual
)
select 
    mycol,
    substr(mycol, 1, instr(mycol, ' - ') - 1) num,
    substr(mycol, instr(mycol, ' - ') + 3) name
from mytable

MYCOL            | NUM | NAME      
:--------------- | :-- | :---------
123 - ABC        | 123 | ABC       
123 - ABC - Corp | 123 | ABC - Corp