oracle根据分隔符将单字段拆分成多行

在数据存储数据库中，常常存在信息以特殊符号隔开形式存储在一个字段里，如下表：

员工姓名	去过的城市
张三	云南、北京、上海
李四	西安、重庆、成都
小花	甘肃、深圳

当我们需要把“去过的城市”字段以逗号（，）拆分成多行时，如下表：

员工姓名	去过的城市
张三	云南
张三	北京
张三	上海
李四	武汉
李四	重庆
李四	西安

这时就会涉及到列转行，下面跟着我的例子，一起来见证奇迹吧：

创建表

CREATE TABLE userinfomation (
  id number(2)  NOT NULL,
  username varchar(200) DEFAULT NULL,
  city varchar(500) DEFAULT NULL
) 

插入测试数据：

insert into userinfomation values(1,'张三','云南、北京、上海');
insert into userinfomation values(2,'李四','西安、重庆、成都');
insert into userinfomation values(3,'小花','甘肃、深圳');

查询sql

将代码拷贝使用时，需要更改表名、字段名、分隔字符等（注：使用的时候还需将将N替换为分隔符的长度，如分割符“、”，长度位1，则将N替换位1,一共4处）。

SELECT id,substr(a.city, instr(a.city, '、', 1, levels.lvl) + N, instr(a.city, '、', 1, levels.lvl + 1) -(instr(a.city, '、', 1, levels.lvl) + 1)-(N-1)) as city,username
FROM
(SELECT id,'、' || city || '、' AS city,username,(length(city || '、') - nvl(length(REPLACE(city, '、')), 0))/N AS cnt
FROM userinfomation) a,
(SELECT rownum AS lvl
FROM (SELECT (MAX(length(city || '、') - nvl(length(REPLACE(city, '、')), 0)))/N max_len FROM userinfomation)
CONNECT BY LEVEL <= max_len) levels
WHERE levels.lvl <= a.cnt
ORDER BY id,levels.lvl;

执行结果

可以从上截图看出，已经将userinfomation表的city 字段根据“、”号分割后，形成多行数据。赶快去试试吧。有问题欢迎评论区留言哦。
借鉴原文来至：

https://blog.youkuaiyun.com/qq_20481125/article/details/99550208， https://www.cnblogs.com/su-root/p/15008584.html