tabname 中有两个字段:
SCLMB_ID SCLMB_XZYS_NAME
1 a,b,c结果为
1 a
1 b
1 cSELECT SCLMB_ID,
LEVEL AS p,
rtrim(regexp_substr(SCLMB_XZYS_NAME || ',', '.*?' || ',', 1, LEVEL),
',') AS cv
FROM tabname
CONNECT BY SCLMB_ID = PRIOR SCLMB_ID
AND PRIOR dbms_random.VALUE IS NOT NULL --主要就这个条件
AND LEVEL <= length(regexp_replace(SCLMB_XZYS_NAME || ',',
'[^' || ',' || ']',
NULL))
start with sclmb_id = '0000000000000000000000000000000000000501'
ORDER BY 1, 2;
--如果是这样,可以不用这个短语,改下SELECT SCLMB_ID,
LEVEL AS p,
rtrim(regexp_substr(SCLMB_XZYS_NAME || ',', '.*?' || ',', 1, LEVEL),
',') AS cv
FROM tabname
CONNECT BY LEVEL <= length(regexp_replace(SCLMB_XZYS_NAME || ',',
'[^' || ',' || ']',
NULL))
start with sclmb_id = '0000000000000000000000000000000000000501'
ORDER BY 1, 2;
--同样为正则表达式,这个简单点:
SQL> with t as(
2 select 1 id,'a,b,c' name from dual)
3 select t.id,a.na
4 from t,(
5 select regexp_substr(name,'[^,]',1,level) na
6 from t
7 connect by
8 level<=length(name)-length(replace(name,',',''))+1) a
9 /
ID NA
---------- ----------
1 a
1 b
1 c
2楼的兄弟这个是不是只能用一个字符的?‘a,b,c’-->'aa,bb,cc'
可否再讲一下
CONNECT BY LEVEL <= length(regexp_replace(SCLMB_XZYS_NAME || ',',
这句还是属于递归吗? 不太理解,
谢谢