REGEXP_SUBSTR(String, pattern, position, occurrence, modifier)
__srcstr :需要进行正则处理的字符串
__pattern :进行匹配的正则表达式
__position :起始位置,从第几个字符开始正则表达式匹配(默认为1)
__occurrence :标识第几个匹配组,默认为1 (该参数可为level从而显示多值:connect by level)
__modifier :模式('i'不区分大小写进行检索;'c'区分大小写进行检索。默认为'c'。)
__example :
test1
ID | NAME |
1 | hhf |
2 | cyl |
XH | SUBJECT |
1,2 | Chinese |
select xh,
(select wm_concat(name) from test1
where id in
(select case when instr(xh,',')=0 then xh else regexp_substr(xh,'[^,]+',1,level) end
from dual
connect by level<=length(xh)-length(replace(xh,',',''))+1))
as name,subject
from test2
**********************************分割****************************************
wm_concat与regexp_substr函数替代:
select xh,ltrim(sys_connect_by_path(name, ','), ',') name from (
select xh,name, row_number() over (partition by xh order by rownum) rn from (
with tt as (select xh,length(xh)-length(replace(xh,',',''))+1 num from test2)
select tt.xh,b.m,(select name from test1
where id=(decode(m,1,nvl(substr(xh,1,instr(xh,',',1,1)-1),xh),
(decode( instr(xh,',',1,b.m),0,substr(xh,instr(xh,',',1,b.m-1)+1),
substr(xh,instr(xh,',',1,b.m)-length(instr(xh,',',1,b.m)-1),instr(xh,',',1,b.m)-1-(instr(xh,',',1,b.m-1))))
)))) name
from tt,
(select rownum m from (select max(num) mm from tt) connect by level<=mm) b
where b.m<=tt.num) where nvl(name,'0')<>'0')
where connect_by_isleaf = 1
start with rn = 1
connect by rn = prior rn + 1 and xh= prior xh
--group by xh
order by 1
------------wm_concat 用 row_number() OVER (PARTITION BY COL1 ORDER BY COL2) +SYS_CONNECT_BY_PATH(COL,str) CONNECT_BY_ISLEAF START WITH CONNECT BY PRIOR
--------------regexp_substr 用 value:substr函数动态截取,result:table 与 level 虚拟表 FULL JOIN