REGEXP_SUBSTR - Most useful with the 'subexpression' parameter!

REGEXP_SUBSTR - you know it, you love it.

Picks up your matches and extracts them just great.

What you may not know, is that you can use an optional parameter to further hone your match down to only the specific portion of the match you want.

Lets imagine, for instance, you have a string such like:

"create table mytb1 as this, create table mytb2 as that, create table mytb3 as hey"

From this string you'd like to extract the table name from each CTAS statement.

Well, one way you might do that is to use REGEXP_SUBSTR to look for every instance of 'create table (.*?) as'.

Using REGEXP_SUBSTR in it's vanilla form, you'd get back your table names but you'd also have to scrape off the 'create table' and 'as' from each match.

Enter the optional, but awesome, subexpression parameter for REGEXP_SUBSTR.

With this parameter, you are able to further hone the very, exact portion of the match you'd like returned.

Notice in the below anonymous block, where I have defined my 'REGEX' variable, that I have it parenthesized. These parenthesized blocks are known as subexpressions of a pattern.

Because I have done this, I can, in the subexpression parameter of the REGEXP_SUBSTR function, identify exactly which subexpression i'd like returned.

Run the below anonymous block example in your DB and what you'll see returned  is:

table name 1 is : mytb1
table name 2 is : mytb2
table name 3 is : mytb3
________________________________________________________________________________

DECLARE
    REGEX     VARCHAR2(4000) := '(create table(.*?) as)';
    TXT CLOB;
BEGIN
    txt :=  'create table mytb1 as this, create table mytb2 as that, create table mytb3 as hey' ;


     FOR i IN 1..regexp_count(TXT, regex, modifier=> 'n')
        LOOP

         DBMS_OUTPUT.PUT_LINE('table name '||i||' is : '|| trim(REGEXP_substr(srcstr => txt
         ,pattern => REGEX
         ,MODIFIER   => 'n' 
         ,subexpression => 2
         ,occurrence => i
          )
)); 

END LOOP;       


END;