oracle中分割字符串，split，regexp_substr

oracle中没有split函数，可以自定义，也可以使用regexp_substr来实现：

根据逗号分割字符串，返回一个table

自定义split

create or replace type type_split as table of varchar2(50);
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;
  return;
end split;

使用regexp_substr

with temp as
 (select 'a,,c,,' as str from dual)
select regexp_substr(replace(str, ',', ' ,'), '[^,]+', 1, level)
  from temp
connect by level <= regexp_count(str, ',') + 1

regexp_count：统计字符出现次数

根据逗号分割字符串，返回第N个

有些字符串不规则，先将,替换成 空格+, 截取返回空格后，再trim去掉

with temp as
 (select 'a,,c,,' as str from dual)
select trim(regexp_substr(replace(str,',',' ,'),'[^,]+',1,1)) from temp 
union all 
select trim(regexp_substr(replace(str,',',' ,'),'[^,]+',1,2)) from temp 
union all 
select trim(regexp_substr(replace(str,',',' ,'),'[^,]+',1,3)) from temp 
union all 
select trim(regexp_substr(replace(str,',',' ,'),'[^,]+',1,4)) from temp