oracle将以逗号分隔字符串转多行将以逗号分隔字符串转多行
不做详解，直接上图。
SELECT T.NAME, T.TESTSTR FROM TEST2 T;
SELECT DISTINCT T.NAME,
REPLACE(REGEXP_SUBSTR(T.TESTSTR, '[^,]+', 1, LEVEL), ',', ' ') TESTSTR
FROM TEST2 T
WHERE 1 = 1
CONNECT BY LEVEL <= (LENGTH(T.TESTSTR) - LENGTH(REPLACE(T.TESTSTR, ',', '')) + 1);
-- Create table TEST2
create table TEST2
(
id NUMBER(10),
name VARCHAR2(100),
teststr VARCHAR2(100)
)
insert into TEST2 (ID, NAME, TESTSTR)
values (2, '徐风年', '洛阳,贾家嘉,姜姒,鱼幼薇,裴南苇,红薯');
insert into TEST2 (ID, NAME, TESTSTR)
values (1, '李淳罡', '剑开天门,两袖青蛇,绿袍儿,剑来');
资源评论
