当前位置:Gxlcms > 数据库问题 > Oracle 实现拆分列数据的split()方法

Oracle 实现拆分列数据的split()方法

时间:2021-07-01 10:21:17 帮助过:511人阅读

‘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, ‘,‘, ‘‘)) + 1  
          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;  

看见的一个博主写的,正好自己能用,先记下,同时感谢这位博主

原链接:http://flforever1213.iteye.com/blog/1026096

Oracle 实现拆分列数据的split()方法

标签:one   connect   实现   --   截取字符串   count()   截取   rownum   nec   

人气教程排行