前几天帮运营查一批商品ID好几百个ID用逗号串在一个Excel单元格里拿过来让我在库里批量查出对应的价格和库存。我当时第一反应是直接拼个IN (1001,1002,1003,...)进去一查零行——Oracle把这个超长字符串当成一个整体而不是三个独立的值。后来老老实实先把字符串拆成多条记录再跟业务表关联一条SQL搞定。类似这种把一个分隔符隔开的结果转成多条结果的需求干Oracle开发的应该都不陌生。无论是前端传参、报表标签列还是LISTAGG聚合之后反查明细本质上都是同一个问题Oracle没有内置的SPLIT或STRING_TO_TABLE需要我们自己用现有函数组合出拆分逻辑。网上讨论很多但不少写法在遇到空值、多行数据、特殊分隔符时会翻车。这篇文章我把这些年实际用过的方案和踩过的坑整理一遍从最经典的正则拆分到12c的JSON_TABLE最后再给几个真实场景的完整SQL。1. 哪些场景逼着你做一拆多先盘一盘需求来源1.1 一串ID传进SQL的日常最典型的场景就是业务方或者前端系统给你传了一串主键值比如1001,1002,1003。你当然可以把这串东西手动拆成一行一个ID再写SQL但几十个几百个就不好玩了。在Oracle里你不能直接WHERE id IN (1001,1002,1003)因为IN列表里的每个元素是独立的一个字符串并不等于三个值。这时候把字符串拆开、变成一张临时结果集再做关联或子查询是最干净的做法。1.2 多值标签字段的建模后遗症有些业务表建表时图省事在一个字段里存了多个标签比如商品表的tags字段可能是新品,热卖,包邮。这种设计做展示很方便一行全出来了但做统计时就麻烦你想知道每个标签分别覆盖了多少商品就必须把每个商品的标签列拆成多行再做GROUP BY。这种多值字段本质上违反了关系型数据库的第一范式但现实里就是存在而且短期还改不了。作为开发你能做的是在查询层把它拆干净而不是天天去催业务改表结构。1.3 LISTAGG 聚合后的后悔药Oracle 11gR2 开始有LISTAGG可以按分组把多行字段拼成一个字符串。比如把每个部门的员工名字拼成一行结果类似SMITH,ALLEN,WARD。问题往往出在下一步这份聚合结果被人拿去当中间数据然后又要回到员工表反查工资、入职日期等明细字段。这时候就得把LISTAGG的成果再拆回多行。这种聚合、拆分、再关联绕了一圈的操作我在实际项目里见过不止一次。1.4 ETL 清洗中的嵌套列表从外部系统导出的 CSV 或者文本文件经常出现某个字段内部又用分号、竖线嵌套了一串值。数据入库前要做标准化把一个字段展开成多列或多行。这类场景同样依赖一拆多。你会发现不管需求包装成什么样SQL 层面的核心动作是一致的拿到一个字符串和分隔符返回一个多行的集合。2. 最常用的拆法REGEXP_SUBSTR CONNECT BY 一条SQL搞定2.1 先看最简单的单串拆分如果你只需要拆一个固定的字符串最经典的写法是这样SELECT regexp_substr(1001,1002,1003, [^,], 1, level) AS id FROM dual CONNECT BY level regexp_count(1001,1002,1003, ,) 1;执行结果1001 1002 1003这个SQL里实际上只用了三个关键点regexp_substr(str, [^,], 1, level)从第一个字符开始按正则[^,]匹配level表示取第几个匹配片段。level 1取第一段level 2取第二段依此类推。这里[^,]的含义很直白匹配一个或多个不是逗号的字符。regexp_count(str, ,)统计整个字符串里有多少个逗号。片段数总是等于分隔符数量加一比如两个逗号会隔出三段。CONNECT BY level n在拆分的场景里它并不是传统意义上的树形查询而是用来生成一个从1到n的数字序列驱动上面的正则表达式反复提取。如果你的字符串是在表里、列里可以先用CTE把字符串准备好WITH t AS ( SELECT A,B,C,D,E AS str FROM dual ) SELECT regexp_substr(str, [^,], 1, level) AS item FROM t CONNECT BY level regexp_count(str, ,) 1;这样就拆出了 A、B、C、D、E 五行。2.2 为什么行数要这样算层级展开的本质很多初学者不理解为什么CONNECT BY level 后面是regexp_count(str, ,) 1而不是别的。你可以把字符串想成一列火车逗号是每节车厢的连接处。一节火车有多少节车厢连接处数量加一。如果字符串是A,B,C两个连接处三节车厢所以最多展开三层层数 level 从1到3。CONNECT BY的level又是从1开始逐层递增的每层都会输出一行因此结果行数恰好等于片段数。这个理解很重要。很多人会把CONNECT BY LEVEL 100理解成循环执行100次从效果上看确实类似但它本质是层次查询层的概念在后面处理多行数据时会引出大坑我下一章专门讲。2.3 拆分顺序由 level 控制别忘记排序在简单场景下regexp_substr配合CONNECT BY level输出的顺序一般是和原字符串一致的。但我在实际使用中发现一旦SQL里加了过滤条件、关联了其他表或者查询计划调整输出顺序可能会变化。如果你对顺序有要求最稳妥的做法是保留 level 列在外面排序SELECT id FROM ( SELECT regexp_substr(1001,1003,1002, [^,], 1, level) AS id, level AS lv FROM dual CONNECT BY level regexp_count(1001,1003,1002, ,) 1 ) ORDER BY lv;为什么我会专门提这个因为有一次我需要按原字符串的顺序给拆出的元素生成序号结果数据一关联顺序完全乱了排查了半天才发现是层级查询的中间结果和业务表做哈希连接后行顺序没有保留。把这个lv字段带出来问题立刻解决。3. 多行同时拆的正确姿势别让裸 CONNECT BY 坑了你3.1 多行数据直接 CONNECT BY 的翻车现场单条字符串用CONNECT BY没问题但如果你照搬到多行数据上就会出大事。看下面这个错误示范WITH t AS ( SELECT A,B AS str FROM dual UNION ALL SELECT C,D,E AS str FROM dual ) SELECT str, regexp_substr(str, [^,], 1, level) AS item FROM t CONNECT BY level regexp_count(str, ,) 1;直觉上应该得到 5 行A、B、C、D、E。但实际跑出来的结果可能不止5行而且item会串行出现 A、B、C、D、E然后还有 A、D、E 这类跨行组合。原因在于CONNECT BY是层次查询在没有指定PRIOR条件时Oracle 会认为任何一行都有可能是其他行的父行。于是第一行展开的树、第二行展开的树在遍历时会互相连接形成类似笛卡尔积的交叉展开。这几乎是所有初学拆分的人最容易踩的坑。3.2 正确方案之一CONNECT BY 加 PRIOR 唯一键隔离要让多行数据互不干扰核心是给每一行加上唯一标识并且在CONNECT BY条件里用PRIOR限定父子行属于同一个标识WITH t AS ( SELECT 1 AS id, A,B AS str FROM dual UNION ALL SELECT 2 AS id, C,D,E AS str FROM dual ) SELECT id, regexp_substr(str, [^,], 1, level) AS item FROM t CONNECT BY level regexp_count(str, ,) 1 AND PRIOR id id;这个写法在部分版本上可行但要小心如果原表里没有唯一键你得先ROW_NUMBER()生成一个而且涉及到PRIOR的树展开性能不一定好尤其在数据量大时容易产生递归开销。Oracle 不同版本对这个写法的行为也略有差异所以我个人不太推荐把它作为首选方案。3.3 正确方案之二推荐关联数字序列表我认为最稳妥、最可控的做法是不依赖CONNECT BY的树遍历而是预先造一张从1到N的数字序列表用 JOIN 条件来控制每行拆出多少个片段。WITH seq AS ( SELECT level AS n FROM dual CONNECT BY level 100 ), t AS ( SELECT 1 AS id, A,B AS str FROM dual UNION ALL SELECT 2 AS id, C,D,E AS str FROM dual ) SELECT t.id, regexp_substr(t.str, [^,], 1, seq.n) AS item FROM t JOIN seq ON seq.n regexp_count(t.str, ,) 1 ORDER BY t.id, seq.n;这个方案的好处非常明显彻底避开了CONNECT BY多行树遍历的交叉展开问题逻辑上一眼就能看懂每一行字符串和序列表里小于等于它的片段数的数字连接seq.n天然承担了第几个片段的序号顺序不会乱性能通常不比裸CONNECT BY差因为展开逻辑变简单了优化器更容易处理序列表可以复用。我在库里一般会建一张dim_seq永久表里面放1到10000的数字很多需要生成序列、补齐行号、位数拆分的场景都能用它比每次临时CONNECT BY生成更省事。用这张序列表时你只需要估算一下业务里最大可能的分隔符数量。比如标签列最多30个标签序列表到100就绝对够用了。3.4 顺带一提的方案三递归 WITH 展开如果你对CONNECT BY有天然的不信任感递归 WITH 也能实现拆分。写法不算短但至少是标准 SQL 的思路可读性尚可WITH t(str) AS ( SELECT A,B,C,D FROM dual ), spl(pos, item, rest) AS ( SELECT 1, substr(str, 1, instr(str || ,, ,) - 1), substr(str, instr(str || ,, ,) 1) FROM t UNION ALL SELECT pos 1, substr(rest, 1, instr(rest || ,, ,) - 1), substr(rest, instr(rest || ,, ,) 1) FROM spl WHERE rest IS NOT NULL AND rest ) SELECT item FROM spl ORDER BY pos;递归的终止条件是rest为空每次从剩余字符串中切出第一段。这个方案对单行和多行都能处理多行时需要在递归里额外加分组键代码会更长。日常拆分我用得比较少但如果你已经熟悉递归 CTE用它替代CONNECT BY也是可以的。4. 数据不干净时怎么办空值、连续分隔符、特殊字符与类型转换4.1[^,]遇到空元素会丢数据正则[^,]里的号要求至少匹配一个非逗号字符。如果源字符串里出现连续的两个逗号比如A,,C中间是一个空元素regexp_substr(A,,C, [^,], 1, level)在取第二段时会直接跳过空串匹配到 C。这就带来了两个问题一是空元素丢了你最后只拿到 A 和 C二是你无法区分这里本来有值但为空和这里本来就什么都没有。如果你拆的是类似张三,李四,,王五这种固定字段中间空掉一个会很致命。如果确实要保留空元素可以把正则改成[^,]*星号允许零个字符匹配SELECT regexp_substr(A,,C, [^,]*, 1, level) AS item FROM dual CONNECT BY level regexp_count(A,,C, ,) 1;这样第二段会返回空显示为 NULL。但要注意当字符串以逗号开头或结尾时首尾也会多出空元素。比如,A会拆出空行和 A。如果你的业务逻辑是保留中间空、忽略首尾空那就在外面过滤掉ITEM IS NOT NULL。4.2 用 SUBSTR INSTR 做纯函数拆分彻底避开正则空值问题如果不想跟正则表达式较劲还有一个完全用基础函数实现的拆分方式用INSTR找分隔符的位置用SUBSTR把两两分隔符之间的内容切出来。SELECT substr( str, CASE WHEN level 1 THEN 1 ELSE instr(str, ,, 1, level - 1) 1 END, CASE WHEN instr(str, ,, 1, level) 0 THEN length(str) - CASE WHEN level 1 THEN 0 ELSE instr(str, ,, 1, level - 1) END ELSE instr(str, ,, 1, level) - CASE WHEN level 1 THEN 0 ELSE instr(str, ,, 1, level - 1) END - 1 END ) AS item FROM (SELECT A,B,,D AS str FROM dual) CONNECT BY level length(str) - length(replace(str, ,, )) 1;这段SQL的思路是每次找到第 level 个分隔符的位置和第 level-1 个分隔符的位置两者之间的就是字段内容如果是第一个字段起点就是1如果是最后一个字段终点就是字符串末尾。好处在于SUBSTRINSTR不涉及正则表达式的元字符概念遇到竖线、点号、反斜杠等特殊分隔符时不需要转义而且能正确处理中间空元素。坏处是SQL可读性差公式写起来容易出错。所以我一般只把它用在数据比较脏、对正确性要求高的核心脚本里。4.3 分隔符是竖线、反斜杠等特殊字符时如果你确定数据里没有空值又不在乎特殊字符转义那正则拆分还是挺省事的。竖线在外面很常见比如日志文件里的a|b|c。用正则拆WITH t(str) AS (SELECT a|b|c FROM dual) SELECT regexp_substr(str, [^|], 1, level) AS item FROM t CONNECT BY level regexp_count(str, \|) 1;这里regexp_count里我也写了\|转义实际上在中括号[^|]里竖线不需要转义但在普通模式里它是或的含义需要写成\|。如果分隔符本身是反斜杠\用正则处理会非常痛苦。这时候我很建议换成SUBSTR INSTR方案或者在数据库里直接创建一个拆分函数从根上消除转义问题。4.4 数字ID拆分后的隐式转换与NULL批量ID拆出来的是字符串如果要和数字主键关联建议显式TO_NUMBER。同时注意拆出的空元素转成数字后是NULL关联时会被忽略掉。如果只要纯数字可以在过滤条件里用REGEXP_LIKE(item, ^[0-9]$)。Oracle 12c R2 以上还有一个VALIDATE_CONVERSION函数可以判断字符串能否转换为数字但对老版本项目不一定适用。初学者最稳妥的办法就是正则过滤数字格式不要依赖隐式转换。4.5 字符串长度超4000怎么办VARCHAR2 在默认数据库参数下上限是4000字节。如果你要拆的字符串超过这个长度通常字段就得是 CLOB。CLOB 和REGEXP_SUBSTR配合很麻烦需要先用DBMS_LOB.SUBSTR分段再拆代码会变得很冗余。我遇到超长字符串时第一反应不是优化拆分SQL而是怀疑表结构设计是不是有问题——一个动辄几千字符的多值字段本来就不适合塞在一个列里。如果只是在报表层遇到一次性的超长串宁可让开发在应用层用代码拆完再传进数据库也别在SQL里硬啃。5. 想一劳永逸管道函数与JSON_TABLE/XMLTABLE的对比和选型5.1 管道函数 split_str如果项目里经常需要拆分我建议直接建一个通用函数把所有边界情况都处理掉。Oracle里最合适的自定义拆分载体是管道函数它可以像表一样被查询。先创建返回类型CREATE OR REPLACE TYPE tp_split_result AS TABLE OF VARCHAR2(4000); /再创建函数CREATE OR REPLACE FUNCTION fn_split_str( p_str IN VARCHAR2, p_sep IN VARCHAR2 DEFAULT , ) RETURN tp_split_result PIPELINED IS v_rest VARCHAR2(4000); v_pos PLS_INTEGER; BEGIN -- 分隔符不允许为空否则直接返回整个字符串 IF p_sep IS NULL THEN PIPE ROW(p_str); RETURN; END IF; v_rest : p_str; -- 输入为空时返回一行 NULL让调用方好统一处理 IF v_rest IS NULL THEN PIPE ROW(NULL); RETURN; END IF; LOOP v_pos : instr(v_rest, p_sep); -- 已经没有分隔符了剩余部分就是最后一段 IF v_pos 0 THEN PIPE ROW(v_rest); EXIT; END IF; -- 切出当前位置到下一个分隔符之间的内容 PIPE ROW(substr(v_rest, 1, v_pos - 1)); v_rest : substr(v_rest, v_pos length(p_sep)); -- 如果剩余部分为空说明尾部还有一个空白段输出它 IF v_rest IS NULL OR v_rest THEN PIPE ROW(NULL); EXIT; END IF; END LOOP; RETURN; END; /调用方式SELECT column_value AS item FROM TABLE(fn_split_str(A,B,C, ,));函数有两个设计决策可以按业务调整输入是 NULL 时返回一行 NULL调用方不需要再额外判空如果字符串尾部有分隔符比如A,B,函数会额外输出一个 NULL保留尾部空元素的信息。如果你不需要保留在查询里加一句WHERE column_value IS NOT NULL即可。管道函数还支持多字符分隔符比如fn_split_str(a||b||c, ||)这在正则方案里要折腾半天在函数里只是换一个参数的问题。5.2 JSON_TABLEOracle 12c以上的优雅写法如果你用的是12c及以上版本字符串又比较干净可以试试JSON_TABLE。思路是先把 CSV 拼成 JSON 数组格式再让 JSON 解析器负责拆分WITH t AS ( SELECT A,B,C AS str FROM dual ) SELECT jt.item FROM t, json_table( [ || replace(t.str, ,, ,) || ], $[*] COLUMNS ( item VARCHAR2(4000) PATH $, ord FOR ORDINALITY ) ) jt ORDER BY jt.ord;这个方案可读性很好解析逻辑交给 JSON 引擎查询顺序用ORDINALITY保留。缺点是如果字符串里出现双引号或反斜杠拼 JSON 前必须做转义数据一旦变脏就比较麻烦。所以它适合那种我能保证分隔符不会出现在值里面的干净ID串。5.3 XMLTABLE能用但要注意转义比JSON_TABLE更早的玩法是XMLTABLE比如用 XQuery 的ora:tokenizeSELECT column_value.getstringval() AS item FROM xmltable(ora:tokenize(A,B,C, ,));这个写法很简短但、、等 XML 保留字符会直接让解析报错数据里哪怕出现一个符号都得先转义。另外ora:tokenize对字符串长度也有限制所以我认为它更适合作为技术演示生产环境里能不用就不用。5.4 选型标准方案适用版本空元素处理特殊字符/转义大数据量性能推荐度regexp_substr connect by11g需用[^,]*或手动处理需留意元字符中常用但注意多行坑substr instr connect by任意版本保留空元素无需转义高最稳适合重要脚本管道函数任意版本按实现保留/忽略无需转义支持多字符分隔符低PL/SQL切换通用工具箱json_table12c保留空元素字符串含引号时麻烦高JSON解析新项目可尝试xmltable10g有空值问题XML保留字符麻烦中不推荐生产我自己的选型习惯可以总结成三条老项目或者数据脏优先SUBSTR INSTR数据干净且量不太大用序列表加REGEXP_SUBSTR临时分析用管道函数因为写起来最短。新项目如果是12c以上数据源可控的情况下JSON_TABLE值得一试。6. 实战案例批量ID关联、LISTAGG逆操作和标签列展开6.1 把一串部门ID拆开后关联统计需求运营给了一串部门编号10,20,30要统计每个部门的员工数量。正确写法是把ID串拆成记录再和部门表、员工表关联WITH ids AS ( SELECT 10,20,30 AS str FROM dual ), dept_ids AS ( SELECT to_number(regexp_substr(str, [^,], 1, level)) AS deptno FROM ids CONNECT BY level regexp_count(str, ,) 1 ) SELECT d.dname, COUNT(e.empno) AS emp_cnt FROM dept d LEFT JOIN emp e ON e.deptno d.deptno WHERE d.deptno IN (SELECT deptno FROM dept_ids) GROUP BY d.dname ORDER BY d.dname;这里把拆分的核心放到dept_ids这个CTE里主查询就很清爽了。实际使用时如果字符串里可能有重复ID记得先DISTINCT或者用WHERE去过滤。6.2 LISTAGG 之后反向关联原表有时候你手里只有一份已经LISTAGG聚合好的数据是别人导出来的比如每个部门的员工名字串。你要反查工资得先拆回去WITH agg AS ( SELECT deptno, listagg(ename, ,) WITHIN GROUP (ORDER BY empno) AS names FROM emp GROUP BY deptno ), emp_list AS ( SELECT a.deptno, regexp_substr(a.names, [^,], 1, seq.n) AS ename FROM agg a JOIN (SELECT level AS n FROM dual CONNECT BY level 100) seq ON seq.n regexp_count(a.names, ,) 1 ) SELECT e.empno, e.ename, e.sal FROM emp_list el JOIN emp e ON e.ename el.ename AND e.deptno el.deptno ORDER BY e.deptno, e.empno;这里有个真实世界的提醒LISTAGG拼好的字符串如果丢了唯一键信息只能靠名字加部门这种组合去匹配万一同部门有同名员工就会重复或错配。所以我在做这类拆分后回表操作前一定会先确认聚合字段组合是否足够唯一如果不够不如直接从原表重新查询不要绕这一圈。6.3 标签字段一行拆成多行做透视商品表如果有一个多值标签字段做标签统计时需要拆开WITH goods AS ( SELECT SKU001 AS sku, 新品,热卖,包邮 AS tags FROM dual UNION ALL SELECT SKU002, 清仓 FROM dual UNION ALL SELECT SKU003, 热卖,包邮 FROM dual ), seq AS ( SELECT level AS n FROM dual CONNECT BY level 100 ) SELECT g.sku, g.tags, regexp_substr(g.tags, [^,], 1, s.n) AS tag FROM goods g JOIN seq s ON s.n regexp_count(g.tags, ,) 1 ORDER BY g.sku, s.n;结果就是每个SKU一行一个标签后面直接GROUP BY tag就能统计每个标签的商品数。如果某个商品的tags字段是 NULLregexp_count(NULL, ,)会返回 NULL这行连接不成立、不做拆分。如果业务上要求 NULL 也显示一行可以把连接条件改成s.n NVL(regexp_count(g.tags, ,), 0) 1。6.4 性能与优化心得最后说几个我实际压测后的体会在大表上做拆分尽量先缩窄数据范围再拆。比如先过滤掉不需要拆的行剩下几千行做拆分比全表拆分快得多。REGEXP系列函数比基础字符串函数成本高。同一个百万行的拆分任务SUBSTR INSTR方案和REGEXP_SUBSTR方案实测能差出两三倍。数据量小无所谓数据量大建议优先用纯函数。如果每天都跑同一个拆分报表别让业务查询直接拆可以写个定时任务把拆分结果落成中间表报表只查中间表。用空间换时间。管道函数虽然调用方便但在大数据量下涉及 PL/SQL 上下文切换性能并不理想。它更适合做临时取数、数据探查这类场景。我自己的日常工作里清理数据用SUBSTR INSTR版本最安心快速取数用序列表加REGEXP_SUBSTR最顺手临时分析就直接套管道函数。关于这个分隔符隔开的结果转成多条结果的需求说到底你能用的方案不少关键是先问清楚三个问题数据里会不会有空值分隔符会不会出现在值内部单条字符串最大有多长这三个答案基本决定了你该选哪条路。别急着抄 SQL先把数据特征摸清楚写出来的脚本才不容易翻车。