MySQL 8中如何在SELECT语句的单个列中使用多个REGEXP_REPLACE实现多规则替换

Absolutely, you don't need a custom MySQL function to handle this multi-rule replacement—MySQL 8 gives you straightforward ways to do this directly in your SELECT statement. Let's break down why your original approach failed, then walk through two reliable solutions.

Why Your Original OR Approach Didn't Work #

Your initial attempt using OR returned 0 or 1 because OR is a logical operator, not a string manipulation tool. When used with string values (like the output of REGEXP_REPLACE), MySQL converts those strings to boolean values: non-empty strings evaluate to 1, empty strings to 0. That's why you got numeric results instead of your desired replacement text.

Solution 1: Nested REGEXP_REPLACE Calls #

You can chain multiple REGEXP_REPLACE functions, where the output of the first becomes the input for the second. This works well for simple, sequential replacement rules:

DROP DATABASE IF EXISTS cbtnuggets;
CREATE DATABASE cbtnuggets;
CREATE TABLE IF NOT EXISTS cbtnuggets.people (firstName varchar(30));
INSERT INTO cbtnuggets.people (firstName) VALUES ('Trevor'), ('Daniel'), ('Sally');

SELECT 
    REGEXP_REPLACE(
        REGEXP_REPLACE(firstName, '^[^T].*', 'NOT TREVOR'),
        '^T.*', 'This is Trevor'
    ) AS modified_firstName
FROM cbtnuggets.people;

How it works:

1. The inner REGEXP_REPLACE replaces any firstName starting with a non-T character with NOT TREVOR.
2. The outer REGEXP_REPLACE then targets remaining values starting with T (i.e., the original Trevor) and replaces them with This is Trevor.

Solution 2: CASE WHEN with REGEXP #

For better readability—especially if you plan to add more replacement rules later—use a CASE statement with REGEXP condition checks. This makes your logic explicit and easy to modify:

DROP DATABASE IF EXISTS cbtnuggets;
CREATE DATABASE cbtnuggets;
CREATE TABLE IF NOT EXISTS cbtnuggets.people (firstName varchar(30));
INSERT INTO cbtnuggets.people (firstName) VALUES ('Trevor'), ('Daniel'), ('Sally');

SELECT
    CASE
        WHEN firstName REGEXP '^T' THEN 'This is Trevor'
        WHEN firstName REGEXP '^[^T]' THEN 'NOT TREVOR'
        ELSE firstName -- Handle edge cases like NULL or empty strings
    END AS modified_firstName
FROM cbtnuggets.people;

How it works:

• Each WHEN clause checks if firstName matches a regex pattern.
• The first matching condition returns the corresponding replacement text.
• The ELSE clause acts as a fallback for values that don't match your rules (like NULL or empty strings).

Expected Output #

Both solutions will return this result:

modified_firstName
-------------------
This is Trevor
NOT TREVOR
NOT TREVOR

Which to Choose? #

• Use nested REGEXP_REPLACE for simple, 2-3 rule scenarios where brevity is preferred.
• Use CASE WHEN for more complex logic, or when you expect to add more replacement rules later—it's easier to read and maintain.

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