oracle拆分逗号分隔字符串 实现split

发布时间:2020-08-11 14:02:49 作者:studyingeveryday
来源:ITPUB博客 阅读:183
最近老有人问题同样的sql 实现方法,itpub 上发一下,照顾百度用户。

如果一个字符串中有像逗号或其它符号分隔,你想把它折分成列,如’first field, second field , third field’,
拆成

first field
second field
third field

第一种 用10G开始支持的正则表达式

anbob@ANBOB>SELECT REGEXP_SUBSTR ('first field, second field , third field', '[^,]+', 1,rownum)
  2  FROM DUAL
  3  CONNECT BY ROWNUM <=
  4  LENGTH ('first field, second field , third field') - LENGTH (REPLACE ('first field, second field , third field', ',', ''))+1;

REGEXP_SUBSTR('FIRSTFIELD,SECONDFIELD,THIRDFIELD','[^,]+',1,ROWNUM)
------------------------------------------------------------------------------
first field
second field
third field

TIP:
REGEXP_SUBSTR 函数是把那个串以正则不是以,(逗号)开头的截取,第二个参数是取第几组,rownum伪列序号,connect 循环 ,循环次数为串总长度-去除分隔符后=几个分隔符 +1

第二种用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,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;

第三,调试

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, ',', 1, tv.lv) + 1,  
  3                          instr(t.vals, ',', 1, tv.lv + 1) -(  
  4                          instr(t.vals, ',', 1, 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));
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


我的原链接在 http://www.anbob.com/archives/221.html
推荐阅读:
  1. Python如何实现按逗号分隔列表
  2. Python cookbook中如何针对任意多的分隔符拆分字符串

免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。

oracle split 分隔

上一篇:如何解决phpcms v9密码错误的问题

下一篇:介绍一下实施成功的云迁移策略

相关阅读

您好,登录后才能下订单哦!

密码登录
登录注册
其他方式登录
点击 登录注册 即表示同意《亿速云用户服务条款》