如何使用SQL查询从指定字符串中提取符合条件的数值并计算总和

嗨，针对你提出的需求——从给定字符串里提取Cash/后面的数值并计算总和，我会根据不同主流SQL方言给出具体实现方案，你可以根据自己使用的数据库选择对应的方法：

需求回顾 #

给定字符串：

'Cash/260 on 6/9/21, Cash/140 on 6/9/21, Cash/200+ 00923 on 6/9/21'
需要提取的数值是260、140、200，最终求和得到600。

1. MySQL 8.0+ 实现 #

MySQL 8.0及以上支持正则表达式函数和递归CTE，我们可以用递归方式逐个提取匹配的数值，再求和：

WITH RECURSIVE extract_numbers AS (
    -- 初始行：提取第一个匹配的数值
    SELECT
        REGEXP_SUBSTR(input_str, 'Cash/(\\d+)', 1, 1, NULL, 1) AS num,
        1 AS match_idx
    FROM (SELECT 'Cash/260 on 6/9/21, Cash/140 on 6/9/21, Cash/200+ 00923 on 6/9/21' AS input_str) t
    UNION ALL
    -- 递归提取后续匹配的数值
    SELECT
        REGEXP_SUBSTR(input_str, 'Cash/(\\d+)', 1, match_idx + 1, NULL, 1) AS num,
        match_idx + 1
    FROM extract_numbers
    JOIN (SELECT 'Cash/260 on 6/9/21, Cash/140 on 6/9/21, Cash/200+ 00923 on 6/9/21' AS input_str) t
    WHERE REGEXP_SUBSTR(input_str, 'Cash/(\\d+)', 1, match_idx + 1, NULL, 1) IS NOT NULL
)
SELECT SUM(CAST(num AS UNSIGNED)) AS total_sum
FROM extract_numbers;

说明： #

• REGEXP_SUBSTR的最后一个参数1表示取正则表达式中第1个捕获组的内容（也就是Cash/后面的数字）
• 递归CTE会不断提取下一个匹配的数值，直到没有匹配项为止
• 最后将提取到的字符串转换为数值类型后求和

2. PostgreSQL 实现 #

PostgreSQL的regexp_matches函数可以直接返回所有匹配的捕获组，结合unnest展开数组后求和：

SELECT SUM(CAST(num AS INT)) AS total_sum
FROM (
    SELECT unnest(regexp_matches(
        'Cash/260 on 6/9/21, Cash/140 on 6/9/21, Cash/200+ 00923 on 6/9/21',
        'Cash/(\\d+)',
        'g'
    )) AS num
) t;

说明： #

• regexp_matches的第三个参数'g'表示全局匹配，返回所有符合条件的捕获组
• unnest将数组转换为行，方便后续聚合求和

3. SQL Server 2017+ 实现 #

SQL Server 2017及以上支持STRING_SPLIT和REGEXP_REPLACE，我们可以先拆分字符串，再提取数值：

SELECT SUM(CAST(REGEXP_REPLACE(value, '.*Cash/(\d+).*', '$1') AS INT)) AS total_sum
FROM STRING_SPLIT('Cash/260 on 6/9/21, Cash/140 on 6/9/21, Cash/200+ 00923 on 6/9/21', ', ')
WHERE value LIKE '%Cash/%';

说明： #

• STRING_SPLIT用, 作为分隔符拆分原字符串，得到每个包含Cash/的子串
• REGEXP_REPLACE通过正则替换，只保留Cash/后面的数字部分
• 过滤出包含Cash/的行后，转换为数值并求和

如果你的数据库版本较低，或者需要兼容其他方言，也可以通过字符串截取函数（比如SUBSTRING、CHARINDEX）来实现，不过正则方式会更简洁灵活。

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