oracle - Need to extract text with REGEXP_SUBSTR - cannot find the right combination

Question

Here is an example of my text- I am trying to get TEXTPART3 AS THE ANSWER:

TEXTPART1 : TEXTPART2: TEXTPART3 - TEXTPART4

I used TRIM(LEADING ':' FROM REGEXP_SUBSTR('textstatementhere', ':.+?-')) - but it is not accounting for the two ":" and the "-" in the text statement I get ' TEXTPART2: TEXTPART3 -'

Can anyone help?

Thanks in advance!

question from:https://stackoverflow.com/questions/65942702/need-to-extract-text-with-regexp-substr-cannot-find-the-right-combination

Answer 1

Leverage the REGEXP_SUBSTR Function to Do All of Your Work

Use a subexpression, (w), and reference it:

WITH exmple AS (
    SELECT
        'TEXTPART1 : TEXTPART2: TEXTPART3 - TEXTPART4' txt
    FROM
        dual
)
SELECT
    txt,
    regexp_substr(txt, ': (w*) -', 1, 1, NULL,
                  1)
FROM
    exmple;

I see that you used ., in lieu of w. Because you chose the meta-character,. (which represents all characters except new line (though that can be included if "n" is set as a pattern matching modifier)), the second colon is thrown in to the matching set.

What does TEXTPART3 include?

Perhaps the meta-character, w, (which stands for alphanumeric or underscore (_) character), is not what you need.

You could replace it with a non-matching character list to avoid the problems with .:

[^:].

This approach would look like this:

REGEXP_SUBSTR(txt, ': ([^:]*) -',1,1,NULL,1)

Lastly, with the quantiifier associated with this subexpression, I used * which means zero or more matches. I assumed that there would be instances where there could be zero matches for this TEXTPART3. If this is not the case, we can use +.