查詢oracle中逗號(hào)分隔字符串中所有值

如果一個(gè)字符串中有像逗號(hào)或其它符號(hào)分隔，你想把它折分成列，如’first field, second field , third field’,

拆成

first field

second field

third field

第一種 用10G開始支持的正則表達(dá)式

SELECT REGEXP_SUBSTR (‘first field, second field , third field’, ‘[^,]+’, 1,rownum)

FROM DUAL

CONNECT BY ROWNUM <=

LENGTH (‘first field, second field , third field’) – LENGTH (REPLACE (‘first field, second field , third field’, ‘,’, ”))

+1

————

first field

second field

third field

REGEXP_SUBSTR 函數(shù)是把那個(gè)串以正則不是以,(逗號(hào))開頭的截取，第二個(gè)參數(shù)是取第幾組，rownum偽列序號(hào)，connect 循環(huán) ，循環(huán)次數(shù)為串總長(zhǎng)度-去除分隔符后=幾個(gè)分隔符 +1

第二種用type,function

第一，先創(chuàng)建一個(gè)Type

CREATE OR REPLACE TYPE type_split IS TABLE OF VARCHAR2 (4000)

第二，創(chuàng)建函數(shù)

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,1,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;

第三，調(diào)試

select * from table(split(’aaa,bbb,ccc’,’,’))

擴(kuò)展 regexp_replace

V字段中每個(gè)值中字符串以，分隔，如果不是以9開頭那組串加‘00’

anbob@NCME>create table testreg(v varchar2(80));

Table created.

anbob@NCME>insert into testreg values(’911,000,12,31′);

1 row created.

anbob@NCME>insert into testreg values(’11911,554000,312,931′);

1 row created.

anbob@NCME>commit;

Commit complete.

anbob@NCME>select ltrim(regexp_replace(‘,’||v,’([,])’,'\100′),’,') newv,v from testreg;

NEWV V

—————————— ——————————

00911,00000,0012,0031 911,000,12,31

0011911,00554000,00312,00931 11911,554000,312,931

總結(jié)

以上是生活随笔為你收集整理的oracle 逗号,查询oracle中逗号分隔字符串中所有值的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。