Oracle regexp_substr提取WHERE子句中lookup_type值的问题

First, let's diagnose why your current query isn't working:

• The regex lookup_type(\s*)=(\s*)''(^''*)'' uses ^ incorrectly. Outside a character class, ^ denotes the start of a string—not a negation. To match characters except a single quote, you need a negated character class like [^'] instead.
• You also weren’t specifying which captured group to extract, so even if the regex matched, it would return the entire matched string rather than just the value inside the quotes.

Corrected SQL Query #

Here’s the working query that returns exactly THE MILK:

SELECT REGEXP_SUBSTR(
    'where lookup_type = ''THE MILK'' ',
    'lookup_type\s*=\s*''([^'']+)''',
    1, 1, NULL, 1
) AS lookup_type_value
FROM DUAL;

How This Works #

Let’s break down the components:

• lookup_type\s*=\s*: Matches the text "lookup_type" followed by optional whitespace (\s*), an equals sign, and more optional whitespace—this handles any spacing variations around the equals sign.
• ''([^'']+)'': Matches the escaped single quotes ('' in Oracle) and captures everything inside them with ([^'']+). The [^'']+ means "one or more characters that are NOT a single quote," ensuring we stop at the closing quote.
• The final parameter 1 tells REGEXP_SUBSTR to return the first captured group (the content inside the parentheses)—this is the value you want.

Handling Variations #

If your WHERE clause might have case differences (like LookUp_Type or LOOKUP_TYPE), add the 'i' match parameter to make the regex case-insensitive:

SELECT REGEXP_SUBSTR(
    'WHERE LookUp_Type = ''THE MILK'' ',
    'lookup_type\s*=\s*''([^'']+)''',
    1, 1, 'i', 1
) AS lookup_type_value
FROM DUAL;

内容的提问来源于stack exchange，提问作者Superdooperhero