Oracle是否有内置函数实现键值字符串的标签映射替换？

需求明确：将逗号分隔的数字键值对字符串（如1=101,2=John,3=43），结合逗号分隔的标签字符串（如ID,Name,Age），把数字键替换为对应顺序的标签，最终得到ID=101,Name=John,Age=43。

Oracle没有单一内置函数直接完成这个需求，但可以通过内置函数组合实现，以下是两种可行方案：

方案1：Oracle 12c及以上版本（使用JSON_TABLE） #

利用JSON_TABLE更简洁地拆分标签字符串，搭配正则拆分键值对、聚合函数拼接结果：

WITH data AS (
    SELECT '1=101,2=John,3=43' AS kv_str, 'ID,Name,Age' AS label_str FROM dual
),
labels AS (
    SELECT 
        ROWNUM AS key_num,
        label
    FROM data,
         JSON_TABLE('["' || REPLACE(label_str, ',', '","') || '"]', '$[*]' COLUMNS label VARCHAR2(100) PATH '$')
),
kv_pairs AS (
    SELECT
        REGEXP_SUBSTR(kv_str, '[^,]+', 1, LEVEL) AS pair,
        REGEXP_SUBSTR(REGEXP_SUBSTR(kv_str, '[^,]+', 1, LEVEL), '^[^=]+') AS key_num,
        REGEXP_SUBSTR(REGEXP_SUBSTR(kv_str, '[^,]+', 1, LEVEL), '[^=]+$') AS value
    FROM data
    CONNECT BY LEVEL <= REGEXP_COUNT(kv_str, ',') + 1
)
SELECT LISTAGG(l.label || '=' || k.value, ',') WITHIN GROUP (ORDER BY k.key_num) AS result
FROM kv_pairs k
JOIN labels l ON k.key_num = l.key_num;

逻辑说明 #

1. data CTE：定义原始的键值对字符串和标签字符串
2. labels CTE：将标签字符串转为JSON数组，用JSON_TABLE拆分为每行一个标签，并用ROWNUM对应数字键（标签顺序和1、2、3...的数字键一一匹配）
3. kv_pairs CTE：用REGEXP_SUBSTR和CONNECT BY拆分键值对字符串，分离出数字键和对应的值
4. 最后通过LISTAGG将标签+值的组合按顺序拼接成最终字符串

方案2：兼容Oracle 11g及旧版本 #

如果无法使用JSON_TABLE，可以用REGEXP_SUBSTR结合CONNECT BY拆分标签字符串：

WITH data AS (
    SELECT '1=101,2=John,3=43' AS kv_str, 'ID,Name,Age' AS label_str FROM dual
),
labels AS (
    SELECT 
        ROWNUM AS key_num,
        REGEXP_SUBSTR(label_str, '[^,]+', 1, ROWNUM) AS label
    FROM data
    CONNECT BY ROWNUM <= REGEXP_COUNT(label_str, ',') + 1
),
kv_pairs AS (
    SELECT
        REGEXP_SUBSTR(kv_str, '[^,]+', 1, LEVEL) AS pair,
        REGEXP_SUBSTR(REGEXP_SUBSTR(kv_str, '[^,]+', 1, LEVEL), '^[^=]+') AS key_num,
        REGEXP_SUBSTR(REGEXP_SUBSTR(kv_str, '[^,]+', 1, LEVEL), '[^=]+$') AS value
    FROM data
    CONNECT BY LEVEL <= REGEXP_COUNT(kv_str, ',') + 1
)
SELECT LISTAGG(l.label || '=' || k.value, ',') WITHIN GROUP (ORDER BY k.key_num) AS result
FROM kv_pairs k
JOIN labels l ON k.key_num = l.key_num;

注意事项 #

• 单纯的REGEXP_REPLACE无法直接完成需求，因为需要动态建立数字键和标签的映射关系，必须先拆分字符串、匹配对应关系后再拼接
• 确保标签字符串的元素数量和键值对的数量一致，否则会出现匹配不全的情况

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