Oracle正则表达式替代PCRE：提取两个@符号间目标子串的实现方法

Got it, since Oracle doesn’t support PCRE-style lookbehind assertions like your example (?<=\d{4}\@).\w+, we can use built-in string functions or Oracle’s own regex capabilities to grab the substring between the first and second @ symbols in your target string 1331@ iwantthis @3ad44@2.

Method 1: Use INSTR + SUBSTR (No Regex Needed) #

This is a reliable, efficient approach using basic string manipulation functions that work in all Oracle versions:

-- Get the raw substring between first and second @ (includes surrounding spaces)
SELECT SUBSTR(
    '1331@ iwantthis @3ad44@2',
    INSTR('1331@ iwantthis @3ad44@2', '@', 1, 1) + 1,
    INSTR('1331@ iwantthis @3ad44@2', '@', 1, 2) - INSTR('1331@ iwantthis @3ad44@2', '@', 1, 1) - 1
) AS extracted_substring
FROM DUAL;

If you want to trim leading/trailing whitespace (to match the iwantthis result from your PCRE example), wrap the whole thing in TRIM():

SELECT TRIM(SUBSTR(
    '1331@ iwantthis @3ad44@2',
    INSTR('1331@ iwantthis @3ad44@2', '@', 1, 1) + 1,
    INSTR('1331@ iwantthis @3ad44@2', '@', 1, 2) - INSTR('1331@ iwantthis @3ad44@2', '@', 1, 1) - 1
)) AS extracted_substring
FROM DUAL;

Breakdown: #

• INSTR(str, '@', 1, 1) locates the position of the first @
• INSTR(str, '@', 1, 2) locates the position of the second @
• SUBSTR starts right after the first @, and pulls characters until just before the second @ (calculated by subtracting the two positions and adjusting for the @ characters themselves)

Method 2: Use Oracle's REGEXP_SUBSTR #

Oracle has its own regex engine (not PCRE, but powerful enough for this task). We can use a capture group to extract the content between the first two @s:

-- Raw substring with spaces
SELECT REGEXP_SUBSTR('1331@ iwantthis @3ad44@2', '@(.*?)@', 1, 1, 'n', 1) AS extracted_substring
FROM DUAL;

-- Trimmed result
SELECT TRIM(REGEXP_SUBSTR('1331@ iwantthis @3ad44@2', '@(.*?)@', 1, 1, 'n', 1)) AS extracted_substring
FROM DUAL;

Breakdown: #

• @(.*?)@ matches a @, then captures any characters (non-greedily, so it stops at the next @) in a group
• The 'n' parameter lets . match newline characters (optional, but adds flexibility)
• The final 1 tells Oracle to return the first capture group (the content between the two @s)

Note: Oracle’s regex doesn’t support lookbehind assertions like (?<=...), which is why we can’t use your original PCRE pattern directly. These workarounds achieve the exact same result for your example.

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