‘one,two,three,four,five,six,seven,eight,nine,zero‘as source_string
from dual),-- 统计字符串中子串的个数,用 ‘,‘来划分子串
T2 as(
select regexp_count(source_string,‘[^,]+‘) as source_substring_count
from T1),--根据子串的个数创建索引列,用于给T4的regexp_substr()方法索引
T3 as(
select rownum as row_number
from dual, T2
connect by rownum<=T2.source_substring_count),--根据每个索引值逐个截取字符串
T4 as(
select T3.row_number as substring_index,
regexp_substr(T1.source_string,‘[^,]+‘, 1, T3.row_number) as substring
from T1, T3)
select substring_index, substring from T4;
鉴于 regexp_count() 方法是 Oracle 11g 才新加上的,之前的版本并没有,这里再用另一种方法来统计子串的个数:
--创建需要划分的字符串
with T1 as(
select‘one,two,three,four,five,six,seven,eight,nine,zero‘as source_string
from dual),--统计字符串中子串的个数-- 字符串中‘,‘字符用‘‘代替后,其减少的长度自然就是原串中‘,‘字符的个数
T2 as(
select length(T1.source_string)- length(replace(T1.source_string, ‘,‘, ‘‘)) + 1as source_substring_count
from T1),--根据子串的个数创建索引列,用于给T4的regexp_substr()方法索引
T3 as(
select rownum as row_number
from dual, T2
connect by rownum<=T2.source_substring_count),--根据每个索引值逐个截取字符串
T4 as(
select T3.row_number as substring_index,
regexp_substr(T1.source_string,‘[^,]+‘, 1, T3.row_number) as substring
from T1, T3)
select substring_index, substring from T4;
看见的一个博主写的,正好自己能用,先记下,同时感谢这位博主
原链接:http://flforever1213.iteye.com/blog/1026096
Oracle 实现拆分列数据的split()方法
标签:one connect 实现 -- 截取字符串 count() 截取 rownum nec
本条技术文章来源于互联网,如果无意侵犯您的权益请点击此处反馈版权投诉 本文系统来源:http://www.cnblogs.com/jianguang/p/6445156.html