如何创建SQL函数实现字符串拆分并统计元素出现次数？

原代码存在两个核心问题：返回结构不符合需求，且逻辑仅统计分隔符数量而非元素出现次数。以下是适配DB2环境的正确实现：

正确函数实现 #

CREATE OR REPLACE FUNCTION SPLIT_STRING_COUNT (
    IN_STRING VARCHAR(1000),
    IN_DELIM VARCHAR(20)
) RETURNS TABLE (
    VALUE VARCHAR(1000),
    COUNTER INT
)
LANGUAGE SQL
READS SQL DATA
BEGIN
    -- 用递归CTE拆分字符串为单个元素
    WITH RECURSIVE STR_SPLIT AS (
        SELECT
            1 AS POS,
            LOCATE(IN_DELIM, IN_STRING) AS DELIM_POS,
            SUBSTR(IN_STRING, 1, LOCATE(IN_DELIM, IN_STRING) - 1) AS ITEM
        FROM SYSIBM.SYSDUMMY1
        WHERE IN_STRING IS NOT NULL AND IN_STRING <> ''
        UNION ALL
        SELECT
            POS + 1,
            LOCATE(IN_DELIM, IN_STRING, DELIM_POS + 1),
            SUBSTR(IN_STRING, DELIM_POS + 1, LOCATE(IN_DELIM, IN_STRING, DELIM_POS + 1) - DELIM_POS - 1)
        FROM STR_SPLIT
        WHERE DELIM_POS > 0
    ),
    -- 处理最后一个无分隔符的元素
    ALL_ITEMS AS (
        SELECT ITEM FROM STR_SPLIT WHERE DELIM_POS > 0
        UNION ALL
        SELECT SUBSTR(IN_STRING, DELIM_POS + 1) FROM STR_SPLIT WHERE DELIM_POS = 0 AND POS = 1
    )
    -- 分组统计每个元素的出现次数
    SELECT ITEM AS VALUE, COUNT(*) AS COUNTER
    FROM ALL_ITEMS
    GROUP BY ITEM
    ORDER BY VALUE;
END;

调用示例 #

SELECT * FROM TABLE(SPLIT_STRING_COUNT('a1,a2,a1,a3', ','));

输出结果 #

关键说明 #

• 递归CTE拆分：通过递归方式逐个定位分隔符位置，提取每个元素，解决字符串拆分问题。
• 边界处理：单独处理最后一个无后续分隔符的元素，避免遗漏。
• 分组统计：对拆分后的元素分组计数，直接生成需求的结果结构。

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