Oracle 实现拆分列数据的split()方法
Sql代码
- -- 创建需要划分的字符串
- with T1 as(
- select '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 才新加上的,之前的版本并没有,这里再用另一种方法来统计子串的个数:
Sql代码
- -- 创建需要划分的字符串
- 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;
运行结果:
运行结果: