[oracle]根据分隔字符将单字段拆分成多行_oracle字段拆分成多行-CSDN博客

本文链接：https://blog.csdn.net/qq_20481125/article/details/99550208

最初这个拆分的语句我也是从别人的博客搜来的，但是基本上只能根据单个分隔字符(即分隔字符的长度为1)来拆分。经过我反复测试，修改了好一些代码，终于成功进行了拓展，分隔字符长度为2、3甚至更多时一样能够成功拆分。所以这篇文章算是原创也说得过去吧？

演示

1.根据单个字符进行拆分

– 创建表

create table myfriendsTest(
       friendid number(18),
       friendname varchar2(500),
       sex varchar2(2)
);

– 添加数据1

truncate table myfriendsTest;
insert into myfriendsTest values(1,'黑狗,蠢驴,饭桶,辣鸡','男');
insert into myfriendsTest values(2,'大明','男');
insert into myfriendsTest values(3,'小花,翠花,阿娇','女');
commit;

– 拆分字符为,即半角下的逗号
在这里插入图片描述
– 开始根据1个字符拆分

SELECT friendid,substr(a.friendname, instr(a.friendname, ',', 1, levels.lvl) + 1, instr(a.friendname, ',', 1, levels.lvl + 1) -(instr(a.friendname, ',', 1, levels.lvl) + 1)-0) as friendname,sex
FROM 
(SELECT friendid,',' || friendname || ',' AS friendname,sex,(length(friendname || ',') - nvl(length(REPLACE(friendname, ',')), 0))/1 AS cnt
FROM myfriendsTest) a,
(SELECT rownum AS lvl 
FROM (SELECT MAX(length(friendname || ',') - nvl(length(REPLACE(friendname, ',')), 0)) max_len FROM myfriendsTest)
CONNECT BY LEVEL <= max_len) levels
WHERE levels.lvl <= a.cnt
ORDER BY friendid;

在这里插入图片描述

2.根据两个个字符进行拆分

– 添加数据2

truncate table myfriendsTest;
insert into myfriendsTest values(1,'黑狗,.蠢驴,.饭桶,.辣鸡','男');
insert into myfriendsTest values(2,'大明','男');
insert into myfriendsTest values(3,'小花,.翠花,.阿娇','女');
commit;

– 拆分字符为,.即半角下的逗号+句号
在这里插入图片描述
– 开始根据2个字符拆分

SELECT friendid,substr(a.friendname, instr(a.friendname, ',.', 1, levels.lvl) + 2, instr(a.friendname, ',.', 1, levels.lvl + 1) -(instr(a.friendname, ',.', 1, levels.lvl) + 1)-1) as friendname,sex
FROM 
(SELECT friendid,',.' || friendname || ',.' AS friendname,sex,(length(friendname || ',.') - nvl(length(REPLACE(friendname, ',.')), 0))/2 AS cnt
FROM myfriendsTest) a,
(SELECT rownum AS lvl 
FROM (SELECT (MAX(length(friendname || ',.') - nvl(length(REPLACE(friendname, ',.')), 0)))/2 max_len FROM myfriendsTest)
CONNECT BY LEVEL <= max_len) levels
WHERE levels.lvl <= a.cnt
ORDER BY friendid;

在这里插入图片描述

3.根据三个字符进行拆分

– 添加数据3

truncate table myfriendsTest;
insert into myfriendsTest values(1,'狗abc蠢驴abc大饭桶abc超级辣鸡','男');
insert into myfriendsTest values(2,'a大明b','男');
insert into myfriendsTest values(3,'花abc小小翠花abc大大阿娇','女');
commit;

– 拆分字符为abc
在这里插入图片描述
– 开始根据3个字符拆分

SELECT friendid,substr(a.friendname, instr(a.friendname, 'abc', 1, levels.lvl) + 3, instr(a.friendname, 'abc', 1, levels.lvl + 1) -(instr(a.friendname, 'abc', 1, levels.lvl) + 1)-2) as friendname,sex
FROM 
(SELECT friendid,'abc' || friendname || 'abc' AS friendname,sex,(length(friendname || 'abc') - nvl(length(REPLACE(friendname, 'abc')), 0))/3 AS cnt
FROM myfriendsTest) a,
(SELECT rownum AS lvl
FROM (SELECT (MAX(length(friendname || 'abc') - nvl(length(REPLACE(friendname, 'abc')), 0)))/3 max_len FROM myfriendsTest)
CONNECT BY LEVEL <= max_len) levels
WHERE levels.lvl <= a.cnt
ORDER BY friendid,levels.lvl;

在这里插入图片描述

使用详解

将代码拷贝下来使用时，除了更改表、字段、分隔字符等，还得修改几处数字。
以例子3为演示
SELECT friendid,substr(a.friendname, instr(a.friendname, ‘abc’, 1, levels.lvl) + 3, instr(a.friendname, ‘abc’, 1, levels.lvl + 1) -(instr(a.friendname, ‘abc’, 1, levels.lvl) + 1)-2) as friendname,sex
FROM
(SELECT friendid,‘abc’ || friendname || ‘abc’ AS friendname,sex,(length(friendname || ‘abc’) - nvl(length(REPLACE(friendname, ‘abc’)), 0))/3 AS cnt
FROM myfriendsTest) a,
(SELECT rownum AS lvl
FROM (SELECT (MAX(length(friendname || ‘abc’) - nvl(length(REPLACE(friendname, ‘abc’)), 0)))/3 max_len FROM myfriendsTest)
CONNECT BY LEVEL <= max_len) levels
WHERE levels.lvl <= a.cnt
ORDER BY friendid,levels.lvl;
假设分隔符长度为n，
红色数字=n，蓝色数字=n-1。