Oracle中REGEXP_SUBSTR正则函数使用异常问题求助

Ah, I get it—your regex is using greedy matching, which is why you're pulling in extra content you don't want. Let's break this down and fix it quickly.

The Problem with Your Current Regex #

Your pattern [,](.*)[(]eno[)] uses .*, a greedy quantifier that matches as many characters as possible. It grabs everything from the first comma after "hello" all the way to the final (eno) in your string—including the unwanted main.proc.standarad_name(ename), segment. That's why your result is longer than expected.

Two Simple, Reliable Fixes #

1. Use a Negated Character Class (Most Precise)

Since your target value sits between a comma and (eno) and doesn't contain commas itself, we can match only non-comma characters to avoid overreaching:

SELECT REGEXP_SUBSTR (
    'hello, main.proc.standarad_name(ename),main.proc.standarad_val(eno)',
    '[,]([^,]+)[(]eno[)]',
    1, 1, 'i', 1 
) FROM DUAL;

• [^,]+ matches one or more characters that are not commas, so it stops exactly at the start of (eno) without including the preceding comma-separated text.

2. Use Lazy (Non-Greedy) Matching

If you prefer sticking with a wildcard but want it to match as little as possible, use .*? instead of .* (supported in Oracle 10g and later):

SELECT REGEXP_SUBSTR (
    'hello, main.proc.standarad_name(ename),main.proc.standarad_val(eno)',
    '[,](.*?)[(]eno[)]',
    1, 1, 'i', 1 
) FROM DUAL;

• .*? is a lazy quantifier—it matches just enough characters to reach the first occurrence of (eno) after a comma, rather than the last one.

Expected Result #

Both queries will return exactly what you want:

main.proc.standarad_val

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