PL/SQL INSTR、SUBSTR和split函数实现

substr(str,index1,n) 取字符串str，位置index1后面的n个字符，index1取1或0都代表第一个字符。

instr(str,sub,dire,times) 获取字符串str，的子串sub的位置。dire取1代表从左到右找，dire取-1代表从右到左找。times代表找第几个。如果times超出范围，函数返回0。

用法对比如下：
substr

SELECT 'substr(''abcdefghi'',1,4)' FUN,substr('abcdefghi',1,4) RESULT FROM dual union all
SELECT 'substr(''abcdefghi'',0,4)' FUN,substr('abcdefghi',0,4) RESULT FROM dual union all
SELECT 'substr(''abcdefghi'',2,4)' FUN,substr('abcdefghi',2,4) RESULT FROM dual ;

instr

SELECT 'INSTR(''ABCABCABCABC'',''A'',1,1)' FUN, INSTR('ABCABCABCABC','A',1,1) RESULT FROM DUAL UNION ALL
SELECT 'INSTR(''ABCABCABCABC'',''A'',1,1)' FUN, INSTR('ABCABCABCABC','A',1,2) RESULT FROM DUAL UNION ALL
SELECT 'INSTR(''ABCABCABCABC'',''A'',1,1)' FUN, INSTR('ABCABCABCABC','A',-1,1) RESULT FROM DUAL UNION ALL
SELECT 'INSTR(''ABCABCABCABC'',''A'',1,1)' FUN, INSTR('ABCABCABCABC','A',-1,2) RESULT FROM DUAL;

 

PL/SQL没有split函数，要使用split函数的时候需要自己写一个。我自己写了一个oracle函数，如下：

create or replace function SPLITER(p_value varchar2 ,
                                    p_split varchar2 := ',' ,times integer := 1)
--参数1 表示字符串，参数2 为分隔符，参数3 为第几个
 return  varchar2 as
  v_idx1       integer;
  v_idx2       integer;
  v_strs_last varchar2(4000 ) := p_value;
begin
  if times= 1 then
    return substr(v_strs_last,1 ,instr(v_strs_last,p_split,1, 1)-1 );
  end if;
    v_idx1 := instr(v_strs_last, p_split, 1,times-1 );
    v_idx2 := instr(v_strs_last, p_split, 1,times);
  if v_idx2= 0 then
    return substr(v_strs_last,v_idx1+1 );
  end if;
  return substr(v_strs_last,v_idx1+ 1,v_idx2-v_idx1-1 );
end SPLITER;

效果如下：

SELECT 'spliter(''111,222,333,444,555'','','',1)' fun,spliter('111,222,333,444,555',',',1) result FROM dual union all
SELECT 'spliter(''111,222,333,444,555'','','',2)' fun,spliter('111,222,333,444,555',',',2) result FROM dual union all
SELECT 'spliter(''111,222,333,444,555'','','',3)' fun,spliter('111,222,333,444,555',',',3) result FROM dual union all
SELECT 'spliter(''111,222,333,444,555'','','',4)' fun,spliter('111,222,333,444,555',',',4) result FROM dual union all
SELECT 'spliter(''111,222,333,444,555'','','',5)' fun,spliter('111,222,333,444,555',',',5) result FROM dual union all
SELECT 'spliter(''111,222,333,444,555'','','',6)' fun,spliter('111,222,333,444,555',',',6) result FROM dual;