Oracle关于多行字符串根据固定的分隔符进行拆分，connect by递归查询的一些用法

一、说点啥

是这样，之前写过根据固定分隔符拆分字符串的做法，简单的可以直接正则+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;