Oracle中将数字列表转换为VARCHAR2列表的技术方案咨询

原生SQL方案（Oracle） #

由于带前导零的数字无法作为数据库的数字字面量（会被视为无效或丢失前导零），所以需要先将数字列表以字符串形式传入，再通过正则替换拼接成IN子句所需的格式：

-- 输入字符串形式的数字列表，输出带单引号的字符串列表
SELECT '''' || REGEXP_REPLACE('123456789012,091234567812,981234567890', '(,)(?=\d)', ''', ''') || '''' AS in_clause_values
FROM dual;

执行后结果：'123456789012', '091234567812', '981234567890'

PL/SQL方案（Oracle） #

可以创建一个自定义函数，快速将逗号分隔的数字字符串转换为IN子句格式：

CREATE OR REPLACE FUNCTION format_in_list(p_input_list IN VARCHAR2) RETURN VARCHAR2 IS
    v_formatted_list VARCHAR2(4000);
BEGIN
    -- 替换逗号为 ''', '''，再首尾添加单引号
    v_formatted_list := '''' || REPLACE(p_input_list, ',', ''', ''') || '''';
    RETURN v_formatted_list;
END;
/

-- 调用示例
SELECT format_in_list('123456789012,091234567812,981234567890') FROM dual;

注意事项 #

• 必须确保原始数字列表以字符串形式传入，否则带前导零的数字会丢失前导零或被数据库判定为无效数字。
• 如果输入列表长度超过4000字符，需要调整函数的返回类型为CLOB。

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