REGEXP_REPLACE and REGEXP_EXTRACT

I have a URI column coming in a log. I have to parse it and remove the certain parts from it and store it in a table. For Example if I have /v7/cp/members/~PERF1SP826T90869AN/options, then I have to store it as /v7/cp/members/*/options. Can I do that using REGEXP_REPLACE?

Also I would like to see if I could store that part that I removed from the URI as another column? For Example from /v7/cp/members/~PERF1SP826T90869AN/options, I should store /v7/cp/members/*/options as a column and PERF1SP826T90869AN in a separate column.

Solution

If you are using Oracle, here's a method:

SQL> with tbl(str) as (
      select '/v7/cp/members/~PERF1SP826T90869AN/options' from dual
    )
    select regexp_replace(str, '(.*?)(/|$)', '*/', 1, 5)   as replaced,
           regexp_substr(str, '(.*?)(/|$)', 1, 5, NULL, 1) as fifth_element
    from tbl;

REPLACED                 FIFTH_ELEMENT
------------------------ -------------------
/v7/cp/members/*/options ~PERF1SP826T90869AN

SQL>