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

[mekaoyou]

今天因工作需要，写了一个小函数，按分隔符截取字符串的，分享出来，希望有需要的朋友能用到。

功能实例： 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;
/

[Alessandro_]

[Quote=引用 1 楼 canhui87 的回复:]

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

[mallity]

学习

[yejihui9527]

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

[mekaoyou]

[Quote=引用 1 楼 canhui87 的回复:]
SQL code
用正则表达式，一步就出来了
select regexp_substr('aaa,bb,ccccc,ddd,vvv','[^,]+',1,3)
from dual
;
REGEX
-----
ccccc
[/Quote]

学习了，确实很牛！

[pw138]

[Quote=引用 1 楼 canhui87 的回复:]

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

[BenChiM888]

--给你举个例子
[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行。

[s_sj_gz_125]

[xiaoyu871225]

老强大了
[Quote=引用 11 楼 benchim888 的回复:]
SQL code


--给你举个例子
[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……
[/Quote]

[xandercheng2]

正则在9i又不支持

[ilovemk]

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

[一颗向往数据的心丶]

表示学习了。

[雪狼__]

[Quote=引用 1 楼 的回复:]

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

没用过这个函数，顶一下

[c991446521]

好强大，学习了

[呵呵112123]

正则把楼主给狠狠地伤害了

[小海葵1]

楼上你好。又看到你了。 老版本上还是能用的。

[呵呵112123]

楼上你好。 这2天没事干，逛论坛了

[没有桃子的阿狸]

引用 1 楼 canhui87 的回复:

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

泪牛满面

[善若止水]

八仙过海，各显神通呀！正则表达式的功能这么强大

[hiyachen]

正则对于比较复杂数据类型的不太好用，比如字段中有逗号，就傻了。

[wangpengpengwang]

楼主也函数是尽自己所能，用正则式是尽工具所能，都牛X