list字符串转为多行

测试view如下

CREATE OR REPLACE VIEW v AS SELECT '0000000008,0000000009,0000000007' AS c1 FROM dual; 

转为多行语句为

SELECT REGEXP_SUBSTR(C1, '[^,]+', 1, LEVEL) VALUE_STR  FROM VCONNECT BY LEVEL <= REGEXP_COUNT(C1, ',') + 1;

用10G的没有REGEXP_COUNT怎么办？很多地方都提供了这种方法

SELECT REGEXP_SUBSTR(C1, '[^,]+', 1, LEVEL) VALUE_STR  FROM VCONNECT BY LEVEL <= LENGTH(C1) - LENGTH(REPLACE(C1, ',', '')) + 1

看上去复杂了点。有没有简单一点点的方法呢？

可以看下面两个语句

REGEXP_REPLACE

SELECT REGEXP_SUBSTR(C1, '[^,]+', 1, LEVEL) VALUE_STR  FROM VCONNECT BY LEVEL <= LENGTH(REGEXP_REPLACE(C1, '[^,]')) + 1;

TRANSLATE

SELECT REGEXP_SUBSTR(C1, '[^,]+', 1, LEVEL) VALUE_STR  FROM VCONNECT BY LEVEL <= LENGTH(TRANSLATE(C1, ',' || C1, ',')) + 1;