REGEXP_SUBSTR函数

oracle (10G+) REGEXP_SUBSTR函数

*分割字符串函数*

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

test2

XH	SUBJECT
1,2	Chinese

Result：1,2  hhf,cyl 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