在BigQuery的REGEXP_REPLACE中实现多个正则匹配项转大写

我尝试用正则将匹配到的字符串转为大写，先后两次尝试都失败了：

第一次代码：

SELECT 
REGEXP_REPLACE(
'I am testing this string',
    '(testing|string)',
    UPPER('\\1')
);

失败原因：SQL函数执行顺序导致UPPER('\\1')先被计算为字符串'\\1'的大写（仍为'\\1'），而非作用于捕获到的分组内容，替换后没有达到预期效果。

第二次代码：

SELECT 
REGEXP_REPLACE(
'I am testing this string',
    '(testing|string)',
    UPPER(REGEXP_EXTRACT('I am testing this string', '(testing|string)'))
);

失败原因：REGEXP_EXTRACT仅返回第一个匹配的内容，导致所有匹配位置都被替换为该内容的大写，无法实现所有匹配项分别转大写的需求。

期望输出：I am TESTING this STRING

正确实现方案 #

方案1：利用正则替换内置转义（适用于PostgreSQL、Hive等引擎）

支持正则转义序列的SQL引擎，可直接在替换模式中使用\U将捕获分组转为大写，同时添加g参数开启全局匹配：

SELECT REGEXP_REPLACE(
    'I am testing this string',
    '(testing|string)',
    '\U\1',
    'g'
);

执行后即可得到期望的输出结果。

方案2：自定义函数实现（适用于MySQL等不支持转义的引擎）

如果你的SQL引擎不支持正则转义序列，可以通过自定义函数逐个替换匹配项并转为大写：

DELIMITER //
CREATE FUNCTION replace_to_upper(input_str TEXT, pattern VARCHAR(255)) 
RETURNS TEXT
DETERMINISTIC
BEGIN
    DECLARE matched_str VARCHAR(255);
    DECLARE result_str TEXT DEFAULT input_str;
    SET matched_str = REGEXP_SUBSTR(result_str, pattern);
    WHILE matched_str IS NOT NULL DO
        SET result_str = REPLACE(result_str, matched_str, UPPER(matched_str));
        SET matched_str = REGEXP_SUBSTR(result_str, pattern, LOCATE(matched_str, result_str) + LENGTH(matched_str));
    END WHILE;
    RETURN result_str;
END //
DELIMITER ;

-- 调用函数
SELECT replace_to_upper('I am testing this string', 'testing|string');

执行后同样能得到目标输出。

内容的提问来源于stack exchange，提问作者maxime.b28