PostgreSQL如何返回行中的所有大写字母

你当前的SQL语句只返回第一个大写字母，是因为substring(name, '([A-Z])')仅会匹配并返回第一个符合规则的字符。要提取字段中所有大写字母，不同数据库有对应的实现方案：

PostgreSQL #

使用regexp_replace函数，将所有非大写字母的字符替换为空，剩下的就是所有大写字母：

SELECT regexp_replace(name, '[^A-Z]', '', 'g') AS all_uppercase_letters
FROM cust;

其中'g'参数表示全局替换，会处理字符串中所有匹配的非大写字母字符。

MySQL（8.0及以上版本） #

MySQL 8.0及之后支持REGEXP_REPLACE函数，用法和PostgreSQL一致：

SELECT REGEXP_REPLACE(name, '[^A-Z]', '', 'g') AS all_uppercase_letters
FROM cust;

如果是MySQL 8.0以下版本，没有内置正则替换函数，可以创建自定义函数实现：

DELIMITER //
CREATE FUNCTION extract_uppercase(str VARCHAR(255)) 
RETURNS VARCHAR(255)
DETERMINISTIC
BEGIN
    DECLARE result VARCHAR(255) DEFAULT '';
    DECLARE i INT DEFAULT 1;
    DECLARE len INT DEFAULT LENGTH(str);
    DECLARE char_val CHAR(1);
    
    WHILE i <= len DO
        SET char_val = SUBSTRING(str, i, 1);
        IF char_val REGEXP '[A-Z]' THEN
            SET result = CONCAT(result, char_val);
        END IF;
        SET i = i + 1;
    END WHILE;
    
    RETURN result;
END //
DELIMITER ;

-- 调用自定义函数
SELECT extract_uppercase(name) AS all_uppercase_letters
FROM cust;

SQL Server #

可以通过递归CTE结合STRING_AGG，或者自定义函数来实现：

方法1：递归CTE #

WITH RecursiveCTE AS (
    SELECT 
        id,
        name,
        SUBSTRING(name, PATINDEX('%[A-Z]%', name), 1) AS upper_char,
        STUFF(name, 1, PATINDEX('%[A-Z]%', name), '') AS remaining_str
    FROM cust
    WHERE PATINDEX('%[A-Z]%', name) > 0
    
    UNION ALL
    
    SELECT 
        id,
        name,
        SUBSTRING(remaining_str, PATINDEX('%[A-Z]%', remaining_str), 1) AS upper_char,
        STUFF(remaining_str, 1, PATINDEX('%[A-Z]%', remaining_str), '') AS remaining_str
    FROM RecursiveCTE
    WHERE PATINDEX('%[A-Z]%', remaining_str) > 0
)
SELECT 
    id,
    STRING_AGG(upper_char, '') AS all_uppercase_letters
FROM RecursiveCTE
GROUP BY id, name;

方法2：自定义函数 #

CREATE FUNCTION dbo.ExtractUppercase(@str NVARCHAR(MAX))
RETURNS NVARCHAR(MAX)
AS
BEGIN
    DECLARE @result NVARCHAR(MAX) = '';
    DECLARE @i INT = 1;
    DECLARE @len INT = LEN(@str);
    
    WHILE @i <= @len
    BEGIN
        IF SUBSTRING(@str, @i, 1) COLLATE Latin1_General_BIN LIKE '[A-Z]'
        BEGIN
            SET @result = @result + SUBSTRING(@str, @i, 1);
        END
        SET @i = @i + 1;
    END
    
    RETURN @result;
END;

-- 调用函数
SELECT dbo.ExtractUppercase(name) AS all_uppercase_letters
FROM cust;

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