Oracle中基于子串拼接列值：将路径ID替换为对应名称

可以通过递归CTE或者分层查询+LISTAGG实现路径中所有ID到Name的替换，以下是两种可行方案：

方案一：递归CTE拆分并替换路径 #

递归CTE可以逐层拆分Path的每个节点，关联表中的Name后再拼接成最终路径：

WITH path_nodes AS (
    -- 初始化：拆分每条记录的第一个有效节点（跳过开头的/）
    SELECT 
        t.id AS original_id,
        t.name AS original_name,
        t.path,
        1 AS level_num,
        REGEXP_SUBSTR(t.path, '[^/]+', 1, 1) AS node_id,
        (SELECT name FROM your_table WHERE id = REGEXP_SUBSTR(t.path, '[^/]+', 1, 1)) AS node_name
    FROM your_table t
    WHERE REGEXP_SUBSTR(t.path, '[^/]+', 1, 1) IS NOT NULL
    UNION ALL
    -- 递归：拆分后续节点
    SELECT 
        p.original_id,
        p.original_name,
        p.path,
        p.level_num + 1,
        REGEXP_SUBSTR(p.path, '[^/]+', 1, p.level_num + 1) AS node_id,
        (SELECT name FROM your_table WHERE id = REGEXP_SUBSTR(p.path, '[^/]+', 1, p.level_num + 1)) AS node_name
    FROM path_nodes p
    WHERE REGEXP_SUBSTR(p.path, '[^/]+', 1, p.level_num + 1) IS NOT NULL
),
path_aggregated AS (
    -- 按原始记录分组，拼接所有节点的Name
    SELECT 
        original_id,
        LISTAGG(node_name, '/') WITHIN GROUP (ORDER BY level_num) AS replaced_path
    FROM path_nodes
    GROUP BY original_id
)
-- 关联原始表输出结果
SELECT t.name, t.id, t.path, pa.replaced_path
FROM your_table t
JOIN path_aggregated pa ON t.id = pa.original_id
ORDER BY t.id;

说明： #

1. path_nodes CTE先拆分每条Path的各个节点（跳过开头的/），同时通过子查询获取每个节点ID对应的Name；
2. path_aggregated CTE按原始记录ID分组，用LISTAGG将节点Name按层级拼接成完整路径；
3. 最后关联原始表输出替换后的结果，对应你需要的目标格式。

方案二：分层查询拆分路径节点 #

利用Oracle的CONNECT BY语法拆分路径，再拼接Name：

WITH path_split AS (
    SELECT 
        t.id AS original_id,
        t.path,
        REGEXP_SUBSTR(t.path, '[^/]+', 1, LEVEL) AS node_id,
        LEVEL AS level_num
    FROM your_table t
    CONNECT BY 
        REGEXP_SUBSTR(t.path, '[^/]+', 1, LEVEL) IS NOT NULL
        AND PRIOR t.id = t.id
        AND PRIOR SYS_GUID() IS NOT NULL -- 避免循环
)
SELECT 
    t.name,
    t.id,
    t.path,
    LISTAGG((SELECT name FROM your_table WHERE id = ps.node_id), '/') WITHIN GROUP (ORDER BY ps.level_num) AS replaced_path
FROM your_table t
JOIN path_split ps ON t.id = ps.original_id
GROUP BY t.id, t.name, t.path
ORDER BY t.id;

说明： #

1. path_split用CONNECT BY LEVEL拆分路径的每个节点，PRIOR SYS_GUID()确保每条记录独立拆分，不会产生交叉；
2. 分组后用LISTAGG拼接每个节点对应的Name，得到最终替换后的路径。

测试验证 #

将上述SQL中的your_table替换为你的实际表名，执行后会输出：

其中replaced_path列就是你需要的目标结果。

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