Oracle正则：如何用单条REGEXP_LIKE匹配两类邮箱并替换

Absolutely, you can match both HREmail@companyabc.com and HREmail@company.com using a single regular expression in Oracle! Let's fix your logic to handle semicolon-separated emails correctly and get the desired output.

Single Regex Pattern for Both Emails #

The trick is making the abc segment optional. Use this pattern (with case-insensitive matching):

HREmail@company(abc)?\.com

• (abc)? tells the regex to match 0 or 1 occurrences of "abc" (covers both domain variants)
• \. escapes the dot (since unescaped dots match any character in regex)
• Add the 'i' modifier to catch lowercase variants like hremail@...

Fixed Replacement Logic #

Your original CASE statement has two key issues:

1. It uses ^ and $ anchors, which only match the entire EMAIL_LIST string—not individual emails within the semicolon-separated list.
2. It appends the IT email even when no HR emails are present, leading to unnecessary duplicates.

Instead, use a nested REGEXP_REPLACE approach to:

1. Replace all instances of either HR email with the IT email
2. Clean up duplicate IT emails (since your sample input has both HR variants, which would become two IT emails otherwise)
3. Trim any leading/trailing semicolons

Here's the working query:

SELECT 
    TRIM(BOTH ';' FROM 
        REGEXP_REPLACE(
            -- First replace all HR emails with IT email
            REGEXP_REPLACE(
                EMAIL_LIST,
                '(;?)HREmail@company(abc)?\.com(;?)',
                '\1ITEmail@company.com\2',
                1, 0, 'i'
            ),
            -- Then collapse duplicate IT emails into one
            '(;ITEmail@company\.com)+',
            ';ITEmail@company.com',
            1, 0, 'i'
        )
    ) AS cleaned_email_list
FROM your_table;

Breakdown of the Query: #

• (;?) captures optional leading/trailing semicolons to preserve separators between remaining emails
• 1, 0 in REGEXP_REPLACE means "start at position 1 and replace all occurrences" (global replace)
• The inner REGEXP_REPLACE swaps every HR email with the IT email
• The outer REGEXP_REPLACE collapses consecutive duplicate IT emails into a single entry
• TRIM(BOTH ';' FROM ...) removes any stray leading/trailing semicolons left after replacement

Example Test #

For your sample input:

'email1@company.com;email2@company.com;hremail@companyabc.com;hremail@company.com;'

This query outputs exactly what you want:

'email1@company.com;email2@company.com;ITEmail@company.com'

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