oracleREGEXP_SUBSTR实现字符串转列

2014-11-24 09:03:29 · 作者: · 浏览: 0

如将字符串‘张三,李四,王五,赵六,'转换成

1. 张三

2.李四

3.王五

4.赵六

REGEXP_SUBSTR

查询语句:

WITH TEST AS (SELECT ‘张三,李四,王五,赵六,' AS STR FROM DUAL)
SELECT DISTINCT REGEXP_SUBSTR(STR, '[^,]+', 1, LEVEL)
FROM TEST
CONNECT BY LEVEL<=LENGTH(STR)-LENGTH(REGEXP_REPLACE(STR,',',''))+1