Regex to match string between opening and closing parenthesises, but not if there is a specific word after opening parenthesis

Question

As per title, I'm trying to perform a regex to match strings between an opening and closing parenthesis, however if the word "SELECT" is directly after the opening parenthesis then I do not want the string to match.

Here's a sample string:

WITH test AS (
    SELECT
        DATE_DIFF(start_date, end_date, DAY),
        DATE_DIFF(start_date
                  ,end_date
                  ,DAY)
    FROM apple
)
,test 2 AS (
    SELECT blah
    FROM blah
)
SELECT
    *
FROM test

At the moment, I am using the following regex (\()(.|\n)*(\)), which will give me the following match (< indicates start of match, > indicates end of match)

WITH test AS <(
    SELECT
        DATE_DIFF(start_date, end_date, DAY),
        DATE_DIFF(start_date
                  ,end_date
                  ,DAY)
    FROM apple
)
,test 2 AS (
    SELECT blah
    FROM blah
)>
SELECT
    *
FROM test

What I am looking for is actually this:

WITH test AS (
    SELECT
        DATE_DIFF<(start_date, end_date, DAY)>,
        DATE_DIFF<(start_date
                  ,end_date
                  ,DAY)>
    FROM apple
)
,test 2 AS (
    SELECT blah
    FROM blah
)
SELECT
    *
FROM test

I am unsure how to add an exception expression to the regex pattern I am using

EDIT: here's a regex builder showing the problem better: regexr.com/6in0s

Answer 1

You can use

\((?!\s*SELECT\b)[^()]*\)

See the regex demo.

Details:

• \( - a ( char
• (?!\s*SELECT\b) - a negative lookahead that fails the match if there are zero or more whitspaces followed with a whole word SELECT immediately to the right of the current location
• [^()]* - zero or more chars other than ( and )
• \) - a ) char.