大数据-玩转数据-oracel字符串分割转化为多列

一、建表

create table split_string_test(
  id integer  primary key,
  test_string varchar2(500)
);
1
2
3
4

二、插入测试数据

insert into split_string_test values(1, '10,11,12,13,14,22');
insert into split_string_test values(2, '22,23,24');
insert into split_string_test values(3, '6,7,8,9');
1
2
3

三、语句

WITH  cntr  AS
( SELECT  LEVEL  AS lvl
  FROM  dual
  CONNECT BY  LEVEL  <= 1 +  (
          SELECT  MAX ( length(a.test_string) - length(replace(a.test_string, ',')) + 1) 
          FROM  split_string_test a
          )                
) 
SELECT  b.id, b.test_string , lvl, REGEXP_SUBSTR( b.test_string, '([^,]+)', 1, lvl)  AS split_str
FROM  split_string_test b , cntr 
where  (lvl  <=  length(b.test_string) - length(replace(b.test_string, ',')) + 1)
and (REGEXP_SUBSTR  ( b.test_string, '([^,]+)', 1, lvl)  IS NOT NULL
OR  b.test_string  IS NULL )
ORDER BY  b.id, lvl;
1
2
3
4
5
6
7
8
9
10
11
12
13
14

注：取字符串分拆最大的数

SELECT  MAX ( length(a.test_string) - length(replace(a.test_string, ',')) + 1)  FROM  split_string_test a
1

或：

SELECT b.id,
       b.test_string,
       lvl,
       REGEXP_SUBSTR(b.test_string, '([^,]+)', 1, lvl) AS split_str
  FROM split_string_test b,
       (SELECT LEVEL AS lvl
          FROM dual
        CONNECT BY LEVEL <= 1 + (SELECT MAX(length(a.test_string) -
                                            length(replace(a.test_string, ',')) + 1)
                                   FROM split_string_test a))
 where (lvl <=
       length(b.test_string) - length(replace(b.test_string, ',')) + 1)
   and (REGEXP_SUBSTR(b.test_string, '([^,]+)', 1, lvl) IS NOT NULL OR
       b.test_string IS NULL)
 ORDER BY b.id, lvl;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

四、结果