如何用SQL提取文本中的数字并求和？期望结果为555

给定目标字符串 'QWXWX/50,GNTGHNT/5,F/500'，需要提取其中的数字并求和得到结果555，以下是针对主流数据库的实现方案：

MySQL 实现 #

WITH RECURSIVE split_strings AS (
    SELECT 
        SUBSTRING_INDEX('QWXWX/50,GNTGHNT/5,F/500', ',', 1) AS part,
        SUBSTRING('QWXWX/50,GNTGHNT/5,F/500', LENGTH(SUBSTRING_INDEX('QWXWX/50,GNTGHNT/5,F/500', ',', 1)) + 2) AS remaining
    UNION ALL
    SELECT 
        SUBSTRING_INDEX(remaining, ',', 1) AS part,
        SUBSTRING(remaining, LENGTH(SUBSTRING_INDEX(remaining, ',', 1)) + 2) AS remaining
    FROM split_strings
    WHERE remaining != ''
)
SELECT SUM(CAST(SUBSTRING_INDEX(part, '/', -1) AS UNSIGNED)) AS total_sum
FROM split_strings;

逻辑说明：通过递归CTE拆分逗号分隔的子串，用SUBSTRING_INDEX提取每个子串斜杠后的数字部分，转换为无符号整数后求和。

SQL Server 实现 #

SELECT SUM(CAST(SUBSTRING(value, CHARINDEX('/', value) + 1, LEN(value)) AS INT)) AS total_sum
FROM STRING_SPLIT('QWXWX/50,GNTGHNT/5,F/500', ',');

逻辑说明：用STRING_SPLIT拆分字符串为行数据，通过CHARINDEX定位斜杠位置，截取后续数字内容并转换为整数后求和。

PostgreSQL 实现 #

方法一：使用split_part #

SELECT SUM(split_part(part, '/', 2)::INT) AS total_sum
FROM UNNEST(STRING_TO_ARRAY('QWXWX/50,GNTGHNT/5,F/500', ',')) AS parts(part);

方法二：使用正则提取 #

SELECT SUM(SUBSTRING(part FROM '/(\d+)' FOR 1)::INT) AS total_sum
FROM UNNEST(STRING_TO_ARRAY('QWXWX/50,GNTGHNT/5,F/500', ',')) AS parts(part);

逻辑说明：先通过STRING_TO_ARRAY将字符串转为数组，UNNEST拆分为行；再用split_part或正则提取斜杠后的数字，转换为整数后求和。

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