with Tab as (select 1 as 序号,N'张三,李四,刘大' as 姓名 from dual union all select 2,N'王五' from dual ) SELECT 序号,REGEXP_SUBSTR(姓名,'[^,]+',1,lev) as 姓名 FROM Tab, (SELECT LEVEL lev FROM dual CONNECT BY LEVEL <= 100) b WHERE (LENGTH(姓名)-LENGTH(REPLACE(姓名,',','')))+1 >=lev ORDER BY 序号,lev /** 序号 姓名 1 1 张三 2 1 李四 3 1 刘大 4 2 王五**/
as
(select 1 as 序号,N'张三,李四,刘大' as 姓名 from dual union all
select 2,N'王五' from dual
)
SELECT
序号,REGEXP_SUBSTR(姓名,'[^,]+',1,lev) as 姓名
FROM Tab,
(SELECT LEVEL lev FROM dual CONNECT BY LEVEL <= 100) b
WHERE (LENGTH(姓名)-LENGTH(REPLACE(姓名,',','')))+1 >=lev
ORDER BY 序号,lev
/**
序号 姓名
1 1 张三
2 1 李四
3 1 刘大
4 2 王五**/