CODEWITHSUNDEEP
sqlregexstringoracle-databaseregexp-substr
Karla P
Karla P

Reputation: 13

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

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?

Upvotes: 1

Views: 511

Answers (2)

Sayan Malakshinov
Sayan Malakshinov

Reputation: 8655

NB: @GMB solution is much better in your simple case. It's an overkill to use regular expressions for that.

tldr;

Usually it's easierr and more readable to use subexpr parameter instead of occurrence in case of such fixed masks. So you can specify full mask: \d+\s*-\s*\S+ ie numbers, then 0 or more whitespace chars, then -, again 0 or more whitespace chars and 1+ non-whitespace characters.

Then we adding () to specify subexpressions: since we need only numbers and trailing non-whitespace characters we puts them into (): '(\d+)\s*-\s*(\S+)'

Then we just specify which subexpression we need, 1 or 2:

SELECT 
REGEXP_SUBSTR(column_value,'(\d+)\s*-\s*(\S+)',1,1,null,1) AS NUM,
REGEXP_SUBSTR(column_value,'(\d+)\s*-\s*(\S+)',1,1,null,2) AS NAME
from table(sys.odcivarchar2list('123 - ABC', '123 - ABC-Corp'));

Result:

NUM        NAME
---------- ----------
123        ABC
123        ABC-Corp

https://docs.oracle.com/database/121/SQLRF/functions164.htm#SQLRF06303

https://docs.oracle.com/database/121/SQLRF/ap_posix003.htm#SQLRF55544

Upvotes: 1

GMB
GMB

Reputation: 222432

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

Upvotes: 1

Related Questions