一、说点啥
是这样,之前写过根据固定分隔符拆分字符串的做法,简单的可以直接正则+connect by 拆分,超长的可以自建函数处理,详细可以看下面这篇:根据固定分隔符拆分字符串为多行的方法。
然后呢,前段日子接触到一个多行字符串的拆分,如下图
二、怎么办
问:正则拆分截取,配合connect by level <= xx ???
select regexp_substr('abc,def,ghi', '[^,]+', 1, level) str from dual connect by level <= regexp_count('abc,def,ghi', '[^,]+');
这种方法对于单行的字符串是有效的,但是当数据的行数大于1 的时候,且不指定递归条件时,我们来看看结果会是怎么样的,如下,两行数据,第一行为'abc',第二行为'一二',分别connect by 自身长度,结果如下
上面的右图为结果的示意图,根据connect by深度优先的特点,数据的顺序就是图中红线的顺序。而这个时候,如果我们制定connect by 的递归条件,即可指定其递归的方向(不知道这么说对不对,但应该是这么个意思),结果如下:
可以看到,我们指定了prior str = str 之后,abc的子节点就一定是abc,而不会是'一二',反之亦然。
而sql语句中如果有这种prior str = str的自循环递归条件的话,就会报一个“ORA-01436: 用户数据中的 CONNECT BY 循环”的错,这时候我们给递归多加一个and条件,prior dbms_random.value is not null,因为random产生的是随机数,两个条件组合后,自循环递归的内容就不是完全一致的了,Oracle就不会再报错(这个我暂时没看到一个准确的说法,暂时是我综合了网上的一些说法和我自己的想法总结的)。然后,我们递归的出口其实是level <= xxx这个条件。
三、解决问题
同样的原理,看回最开始的需求,同样的方式去实现,代码及结果如下图所示:
代码也贴一贴吧
with t1 as(
select '张三' 姓名, '唱歌、跳舞' 爱好 from dual
union all
select '李四', '吃饭、睡觉、打张三' from dual
)
select 姓名, level, regexp_substr(爱好, '[^、]+', 1, level) 爱好
from t1
connect by level <= regexp_count(爱好, '[^、]+')
and prior 姓名 = 姓名
and prior dbms_random.value is not null;