Oracle 11g 开发统计逗号分隔字符串去重值数量的自定义函数咨询

核心实现适配说明 #

• 完全基于Oracle 11g原生语法开发，不依赖12c及以上版本才支持的JSON_TABLE、原生字符串拆分函数
• 无需创建额外的自定义集合类型，编译后即可直接调用
• 覆盖常见边界场景：入参空值、连续分隔符、首尾带分隔符、拆分值前后带多余空格

函数逻辑拆解 #

• 入参判空：若传入的待拆分字符串为NULL，直接返回0，避免无意义的计算
• 字符串拆分：通过正则提取配合CONNECT BY分层查询，将整段字符串按指定分隔符拆分为独立值片段
• 空值过滤：自动剔除拆分过程中产生的无效空字符串片段
• 去重计数：对所有有效片段执行DISTINCT去重后统计总数量
• 异常兜底：捕获拆分过程中的运行时异常，避免函数抛出不可控错误

具体实现代码 #

CREATE OR REPLACE FUNCTION fn_dist_count_values_in_list_arr(
    p_input_str  IN VARCHAR2,
    p_delimiter  IN VARCHAR2 DEFAULT ','
) RETURN NUMBER
IS
    v_result_count NUMBER;
BEGIN
    -- 空入参直接返回0
    IF p_input_str IS NULL THEN
        RETURN 0;
    END IF;

    -- 执行拆分与去重统计
    SELECT COUNT(DISTINCT split_val)
      INTO v_result_count
      FROM (
            SELECT TRIM(
                        REGEXP_SUBSTR(
                            p_input_str,
                            '[^'||p_delimiter||']+',
                            1,
                            LEVEL
                        )
                    ) AS split_val
              FROM DUAL
            CONNECT BY LEVEL <= REGEXP_COUNT(p_input_str, p_delimiter) + 1
            -- 规避CONNECT BY因重复值导致的死循环问题
               AND PRIOR SYS_GUID() IS NOT NULL
           ) all_split_vals
     WHERE split_val IS NOT NULL;

    RETURN v_result_count;
EXCEPTION
    WHEN OTHERS THEN
        RETURN NULL;
END fn_dist_count_values_in_list_arr;
/

效果验证 #

按照需求给定的测试场景，执行如下调用语句：

SELECT fn_dist_count_values_in_list_arr(
    'Lorem,Ipsum,is,simply,dummy,text,Lorem,Ipsum,is,simply,dummy,text,Lorem,Ipsum,is,simply,dummy,text'
) AS distinct_value_count
FROM DUAL;

执行后返回结果为6，与预期完全匹配。

额外兼容说明：函数支持自定义分隔符，例如需要按竖线|拆分时，可传入第二个参数'|'，无需修改函数主体逻辑。当输入为,a,,b,a,这类带连续逗号、首尾带逗号的场景时，会自动过滤空值，正确统计独立值为2。

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