Oracle字符串转换成多行

最新推荐文章于 2024-08-17 08:00:00 发布

hello_world_kidd

最新推荐文章于 2024-08-17 08:00:00 发布

阅读量826

点赞数

分类专栏： Oracle 文章标签： oracle

Oracle 专栏收录该内容

8 篇文章 0 订阅

订阅专栏

create table T_STR_COL
(
ID INTEGER,
C123 VARCHAR2(32)

)

insert into T_STR_COL (ID, C123)
values (1, 'v11,v21,v31');
insert into T_STR_COL (ID, C123)
values (2, 'v12,v22');
insert into T_STR_COL (ID, C123)
values (3, 'v13,v33');
insert into T_STR_COL (ID, C123)
values (4, 'v24,v34');
insert into T_STR_COL (ID, C123)
values (5, 'v15');
insert into T_STR_COL (ID, C123)
values (6, 'v35');
insert into T_STR_COL (ID, C123)
values (7, null);
commit;

SELECT * FROM T_STR_COL;

SELECT T1.ID, REGEXP_SUBSTR(T1.C123, '[^,]+', 1, T2.LEV) REGION_ID
FROM (SELECT T.*,
               LENGTH(T.C123) - LENGTH(REPLACE(T.C123, ',', '')) + 1 RN
          FROM T_STR_COL T) T1,
       (SELECT LEVEL LEV
          FROM DUAL
        CONNECT BY LEVEL <= (SELECT SUM(LENGTH(T.C123) -
                                        LENGTH(REPLACE(T.C123, ',', '')) + 1) RN
                               FROM T_STR_COL T)) T2
WHERE T2.LEV <= T1.RN
ORDER BY ID, LEV