如何用单条SQL查询将管道分隔字符串替换为另一表对应值？

有两张Oracle表，需使用单条SQL查询，将表A中管道分隔（|）的字符串值替换为表B中的对应值。

表结构与测试数据 #

create table a( col1 Varchar2(100), col2 Varchar2(250));   
insert into a values ('EMP', '1234|5678|9123');   

create table b ( col1 Varchar2(100), col2 Varchar2(250));  
insert into b values ('1234' , 'Testing1');
insert into b values ('5678', 'Values2');
insert into b values ('9123' , 'Agree1');

期望输出 #

通过字符串拆分+关联查询+聚合拼接的方式实现，单条SQL如下：

SELECT 
    a.col1,
    LISTAGG(b.col2, '|') WITHIN GROUP (ORDER BY pos) AS col2
FROM 
    a,
    LATERAL (
        SELECT 
            REGEXP_SUBSTR(a.col2, '[^|]+', 1, LEVEL) AS id,
            LEVEL AS pos
        FROM 
            dual
        CONNECT BY 
            REGEXP_SUBSTR(a.col2, '[^|]+', 1, LEVEL) IS NOT NULL
    ) split_data
LEFT JOIN 
    b ON split_data.id = b.col1
GROUP BY 
    a.col1;

代码说明 #

1. 拆分字符串：利用REGEXP_SUBSTR结合CONNECT BY层级查询，将表A的col2按|拆分成多行，用LEVEL记录拆分项的位置，保证拼接顺序与原字符串一致。
2. 关联替换：将拆分出的ID与表B的col1关联，获取对应的替换值。
3. 聚合拼接：按表A的col1分组，通过LIST_AGG将替换后的值按原顺序用|拼接回字符串。

若表A的col2存在表B中未匹配的ID，可将b.col2替换为NVL(b.col2, split_data.id)，保留原ID而非显示NULL。

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