在Oracle数据库日常的数据处理任务中，我们经常会遇到一种典型需求：某一列里存了用逗号或其他符号分隔的字符串，需要把它拆成多行来做关联查询，或者反过来把多行数据拼成特定格式的字符串。这种转换如果交给应用程序循环处理，既浪费网络往返又增加代码复杂度。利用Oracle内置的正则函数和层级查询，完全可以用一条SQL搞定。
一、使用regexp_substr与connect by拆分字符串
regexp_substr是Oracle提供的正则截取函数，它可以根据模式从源字符串中提取第n个匹配项。配合connect by level的层级生成机制，就能把分隔字符串逐段拆出来。核心思路是：利用level伪列产生连续数字，作为regexp_substr的occurrence参数，直到拆不出内容为止。
下面这段示例把部门表中的skills字段（形如Java,Python,SQL）拆成每行一个技能：
select dept_id, trim(regexp_substr(skills, '[^,]+', 1, level)) as skill_item from dept_table connect by level <= regexp_count(skills, ',') + 1 and prior dept_id = dept_id and prior sys_guid() is not null order by dept_id, level;
这里regexp_count用来计算分隔符数量，从而确定要循环的次数。prior sys_guid() is not null是一个常用技巧，避免connect by出现循环报错。拆分后每一个skill_item都是独立一行，可以直接和技能表做join。
这种写法的优点是纯SQL、无需建存储过程；缺点是当原表数据量大且字符串很长时，connect by会产生较多递归行，建议先在子查询里限定更少的数据集再拆分。
二、将多行数据聚合为指定格式字符串
拆分做完关联后，往往还要把结果重新拼回去，这时就要用到行转列的聚合函数。11g之后Oracle提供了listagg，它可以把分组内的多行表达式连接成一个字符串，并指定分隔符和排序。
以下示例将上面拆出的技能按部门重新拼成有序字符串：
select
dept_id,
listagg(skill_item, '|') within group (order by skill_item) as merged_skills
from (
select
dept_id,
trim(regexp_substr(skills, '[^,]+', 1, level)) as skill_item
from dept_table
connect by level <= regexp_count(skills, ',') + 1
and prior dept_id = dept_id
and prior sys_guid() is not null
)
group by dept_id;
listagg的within group子句定义了拼接顺序，这比老版本用wm_concat要可靠得多，因为wm_concat是非公开函数，不同版本行为不一致。在12c以前listagg结果受varchar2长度限制，超长会报ORA-01489，此时可改用xmlagg做大字段拼接。
从维护角度看，把拆分与聚合写在同一个SQL嵌套里，逻辑集中、执行计划可被优化器统一处理，比在代码里循环拼接更容易调优。
三、常见误区与替代方案
不少人在写connect by拆分时忘记加prior条件，导致和原表做笛卡尔关联，数据量瞬间膨胀几十倍。还有人用instr加substr手动算位置，代码冗长且容易越界。如果数据库版本在18c以上，也可以考虑使用json_table把字符串先转成json数组再展开，可读性更好。
示例：把逗号串包装成json数组后用json_table拆行。
select dept_id, skill_item
from dept_table,
json_table(
'["' || replace(skills, ',', '","') || '"]',
'$[*]' columns skill_item varchar2(50) path '$'
);
这种方式规避了正则开销，但对输入字符串的合法性要求更高，若技能本身含双引号需要提前转义。综合来看，regexp_substr加connect by仍是兼容性最广的做法，理解其原理后稍加封装就能应对绝大多数Oracle字符串与多行互转场景。