oracle拆分逗号分隔字符串 实现split
最近老有人问题同样的sql 实现方法,itpub 上发一下,照顾百度用户。 如果一个字符串中有像逗号或其它符号分隔,你想把它折分成列,如’first field,second field,third field’,
第一种 用10G开始支持的正则表达式 anbob@ANBOB>SELECT REGEXP_SUBSTR ('first field,third field','[^,]+',1,rownum)
2 FROM DUAL
3 CONNECT BY ROWNUM <=
4 LENGTH ('first field,third field') - LENGTH (REPLACE ('first field,',''))+1;
REGEXP_SUBSTR('FIRSTFIELD,SECONDFIELD,THIRDFIELD',ROWNUM)
------------------------------------------------------------------------------
first field
second field
third field
TIP: 第二种用type,function 第一,先创建一个Type CREATE OR REPLACE TYPE type_split IS TABLE OF VARCHAR2 (4000)
第二,创建函数 create or replace function split(p_list varchar2,p_sep varchar2 := ’,’)
return type_split pipelined
IS
l_idx pls_integer;
v_list varchar2(50) := p_list;
begin
loop
l_idx := instr(v_list,p_sep);
if l_idx = 0 then
pipe row(substr(v_list,l_idx-1));
v_list := substr(v_list,l_idx+length(p_sep));
else
pipe row(v_list);
exit;
end if;
end loop;
end split;
第三,调试 select * from table(split(‘aaa,bbb,ccc’,’)); 拆分列字段 anbob@ANBOB>select * from test11;
ID NAME
-------------------- ----------
1 a,b,c
2 e,d
nbob@ANBOB>with temp0 as (select LEVEL lv from dual CONNECT BY LEVEL <= 100)
2 select id,substr(t.vals,instr(t.vals,tv.lv) + 1,3 instr(t.vals,tv.lv + 1) -(
4 instr(t.vals,tv.lv) + 1)
5 ) AS name
6 from (select id,' || name || ',' AS vals,7 length(name || ',') - nvl(length(REPLACE(name,')),0) AS cnt
8 from test11) t join temp0 tv
9 on tv.lv <= t.cnt order by 1;
ID NAME
-------------------- ------------------------
1 a
1 b
1 c
2 d
2 e
扩展 regexp_replace V字段中每个值中字符串以,分隔,如果不是以9开头那组串加‘00’ anbob@NCME>create table testreg(v varchar2(80)); anbob@NCME>insert into testreg values(’11911,554000,312,931′); anbob@NCME>commit; anbob@NCME>select ltrim(regexp_replace(‘,’||v,’([,])’,'100′),’,') newv,v from testreg; NEWV V 我的原链接在http://www.anbob.com/archives/221.html (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |