Oracle函数，按分隔符截取字符串

Question

今天因工作需要，写了一个小函数，按分隔符截取字符串的，分享出来，希望有需要的朋友能用到。功能实例：  substrbysep('aaa,bb,ccccc,ddd,vvv',',',3)返回结果 'ccccc'.函数：create or replace function substrbysep(sourceString    varchar2,
                                       destString      varchar2,
                                       appearPosition  number)
  return varchar2 is
  substring varchar2(255);
  
  --作者：Alex Liu
  --功能：按分隔符截取字符串
  --参数：1、原始字符串；2、分隔符；3、截取第几段
  
begin
  substring := substr(destString || sourceString || destString,
                      instr(destString || sourceString || destString,
                            destString,
                            1,
                            appearPosition) + 1,
                      instr(destString || sourceString || destString,
                            destString,
                            1,
                            appearPosition + 1) -
                      instr(destString || sourceString || destString,
                            destString,
                            1,
                            appearPosition) - 1);
  return(substring);
end substrbysep;
/

Answer 1

用正则表达式，一步就出来了
select regexp_substr('aaa,bb,ccccc,ddd,vvv','[^,]+',1,3)
from dual
;
REGEX
-----
ccccc

Answer 2

substring := substr(sourceString,
  instr(sourceString,
  destString,
  1,
  appearPosition - 1) + 1,
  instr(sourceString ,
  destString,
  1,
  appearPosition) -
  instr(sourceString,
  destString,
  1,
  appearPosition - 1) - 1);
  return(substring);

Answer 3

--给你举个例子
[SYS@orcl] SQL>WITH t1 AS(
  2    SELECT 'aasd ,asd 233,45667,23fdsf,  sdfsdf, sdf235' AS col1 FROM dual
  3    UNION ALL
  4    SELECT '123 ,345 123,3521' AS col1 FROM dual
  5    UNION ALL
  6    SELECT 'ASDGC1,213 123344,45,56 567 67,67,78 78,98' AS col1 FROM dual
  7    UNION ALL
  8    SELECT 'AD,ASD,DG,DG,FG,H,WR,234,234,5,4,YF,GFDGD GD,FG DFG,DFG D' AS col1 FROM dual
  9  )SELECT T1.COL1, REGEXP_SUBSTR(COL1, '[^, ]+', 1, L) AS ANS
 10    FROM T1, (SELECT LEVEL L FROM DUAL CONNECT BY LEVEL <= 100) T2
 11   WHERE LENGTH(T1.COL1) - LENGTH(REPLACE(T1.COL1, ',')) + 1 >= T2.L
 12   ORDER BY 1;COL1                                                         ANS
------------------------------------------------------------ -----------------------
123 ,345 123,3521                                            123
123 ,345 123,3521                                            123
123 ,345 123,3521                                            345
AD,ASD,DG,DG,FG,H,WR,234,234,5,4,YF,GFDGD GD,FG DFG,DFG D    AD
AD,ASD,DG,DG,FG,H,WR,234,234,5,4,YF,GFDGD GD,FG DFG,DFG D    GD
AD,ASD,DG,DG,FG,H,WR,234,234,5,4,YF,GFDGD GD,FG DFG,DFG D    FG
AD,ASD,DG,DG,FG,H,WR,234,234,5,4,YF,GFDGD GD,FG DFG,DFG D    GFDGD
AD,ASD,DG,DG,FG,H,WR,234,234,5,4,YF,GFDGD GD,FG DFG,DFG D    YF
AD,ASD,DG,DG,FG,H,WR,234,234,5,4,YF,GFDGD GD,FG DFG,DFG D    4
AD,ASD,DG,DG,FG,H,WR,234,234,5,4,YF,GFDGD GD,FG DFG,DFG D    5
AD,ASD,DG,DG,FG,H,WR,234,234,5,4,YF,GFDGD GD,FG DFG,DFG D    234
AD,ASD,DG,DG,FG,H,WR,234,234,5,4,YF,GFDGD GD,FG DFG,DFG D    234
AD,ASD,DG,DG,FG,H,WR,234,234,5,4,YF,GFDGD GD,FG DFG,DFG D    WR
AD,ASD,DG,DG,FG,H,WR,234,234,5,4,YF,GFDGD GD,FG DFG,DFG D    H
AD,ASD,DG,DG,FG,H,WR,234,234,5,4,YF,GFDGD GD,FG DFG,DFG D    FG
AD,ASD,DG,DG,FG,H,WR,234,234,5,4,YF,GFDGD GD,FG DFG,DFG D    DG
AD,ASD,DG,DG,FG,H,WR,234,234,5,4,YF,GFDGD GD,FG DFG,DFG D    DG
AD,ASD,DG,DG,FG,H,WR,234,234,5,4,YF,GFDGD GD,FG DFG,DFG D    ASD
ASDGC1,213 123344,45,56 567 67,67,78 78,98                   56
ASDGC1,213 123344,45,56 567 67,67,78 78,98                   123344
ASDGC1,213 123344,45,56 567 67,67,78 78,98                   ASDGC1
ASDGC1,213 123344,45,56 567 67,67,78 78,98                   213
ASDGC1,213 123344,45,56 567 67,67,78 78,98                   67
ASDGC1,213 123344,45,56 567 67,67,78 78,98                   45
ASDGC1,213 123344,45,56 567 67,67,78 78,98                   567
aasd ,asd 233,45667,23fdsf,  sdfsdf, sdf235                  asd
aasd ,asd 233,45667,23fdsf,  sdfsdf, sdf235                  sdfsdf
aasd ,asd 233,45667,23fdsf,  sdfsdf, sdf235                  45667
aasd ,asd 233,45667,23fdsf,  sdfsdf, sdf235                  233
aasd ,asd 233,45667,23fdsf,  sdfsdf, sdf235                  aasd
aasd ,asd 233,45667,23fdsf,  sdfsdf, sdf235                  23fdsf已选择31行。

Answer 4

lz的代码在10g之前还是有参考价值的