测试view如下
看上去复杂了点。有没有简单一点点的方法呢?
CREATE OR REPLACE VIEW v AS
SELECT '0000000008,0000000009,0000000007' AS c1 FROM dual;
转为多行语句为
SELECT REGEXP_SUBSTR(C1, '[^,]+', 1, LEVEL) VALUE_STR
FROM V
CONNECT BY LEVEL <= REGEXP_COUNT(C1, ',') + 1;
用10G的没有REGEXP_COUNT怎么办?很多地方都提供了这种方法
SELECT REGEXP_SUBSTR(C1, '[^,]+', 1, LEVEL) VALUE_STR
FROM V
CONNECT BY LEVEL <= LENGTH(C1) - LENGTH(REPLACE(C1, ',', '')) + 1
看上去复杂了点。有没有简单一点点的方法呢?
可以看下面两个语句
REGEXP_REPLACE
SELECT REGEXP_SUBSTR(C1, '[^,]+', 1, LEVEL) VALUE_STR
FROM V
CONNECT BY LEVEL <= LENGTH(REGEXP_REPLACE(C1, '[^,]')) + 1;
SELECT REGEXP_SUBSTR(C1, '[^,]+', 1, LEVEL) VALUE_STR
FROM V
CONNECT BY LEVEL <= LENGTH(TRANSLATE(C1, ',' || C1, ',')) + 1;