Regex: Get penultimate part of a "path"

user_162414 0
Viewed 156

I've got something like this:

>AAA>BBB>CCC>DDD

With

([^>]*$)

I get the last part DDD . How can I get the part before it, CCC?

Thanks!

oracle regex
3 Answers

You may use

REGEXP_SUBSTR('>AAA>BBB>CCC>DDD', '([^>]+)>[^>]+$', 1, 1, NULL, 1)

The ([^>]+)>[^>]+$ regex will match and capture into Group 1 any 1+ chars other than >, then will match > followed with any 1+ chars other than > up to the end of the string.

The last argument, 1, tells REGEXP_SUBSTR to return just the captured substring.

See online demo.

Another approach is to replace the whole string but keep the captured part of your choice:

REGEXP_REPLACE( '>AAA>BBB>CCC>DDD', '.*>([^>]+)>[^>]+$', '\1')

See another online demo.

Here, .*> will match all the string up to the >, then ([^>]+) will capture any 1+ chars other than > and then >[^>]+$ will match and consume > and 1+ chars other than > at the end of the string.

user_38329700

You don't need regular expressions for this - standard string functions suffice, and they will be much faster.

In the last example, notice that there is no "second-to-last" or penultimate part; so the output is NULL. That is indeed the correct answer in that case.

with
  test_data (pth) as (
    select '>AAA>BBB>CCC>DDD' from dual union all
    select null               from dual union all
    select '>EEE>GGG'         from dual union all
    select '>JJJJJ'           from dual
  )
select pth,
       substr(pth, instr(pth, '>', -1, 2) + 1, 
              instr(pth, '>', -1, 1) - instr(pth, '>', -1, 2) - 1) as stl
from   test_data
;

PTH               STL             
----------------  ----------------
>AAA>BBB>CCC>DDD  CCC             

>EEE>GGG          EEE             
>JJJJJ
Admin1

Here is a silly workaround for the lack of support for returning subexpressions in your version of Oracle. I offer this just as a curiosity; I proposed a better solution that doesn't use regular expressions at all in a separate Answer.

with
  test_data (pth) as (
    select '>AAA>BBB>CCC>DDD' from dual union all
    select null               from dual union all
    select '>EEE>GGG'         from dual union all
    select '>JJJJJ'           from dual
  )
select pth, 
       regexp_substr(pth, '[^>]*', 1, nullif(2*regexp_count(pth, '>')-2, 0)) as stl
from   test_data
;

PTH              STL             
---------------- ----------------
>AAA>BBB>CCC>DDD CCC             

>EEE>GGG         EEE             
>JJJJJ
Admin1
Related