oracle截取及拼接,Oracle字符串截取和拼接应用

今天有人问了个关于Oracle字符串截取和拼接的问题，让我帮他写出SQL，看了下问题描述还比较清晰就试着解决下，利用午休时间把功能实现了，问题看似不难，但思路一定要清晰，不然就乱了，关键大量应用了Oracle的substr 和instr函数。下面贴出问题和脚本：

问题：sql中一个字段值为：1788987565327、768374872394903、21437238740213483874629、23412341234252345。其中顿号间隔的每一组数字位数和尾数不定，现在要使前面这个字段值中顿号前的数字尾数即7、3、9、5都分别加1，变成8、4、0、6输出成1788987565328、768374872394904、21437238740213483874620、23412341234252346。注意其中第三个数，从9加1后，输出成0，而不是10

脚本：

declare

targetstr varchar2(2000);

strlength number;

position number;

maxposition number;

retrunstr varchar2(2000);

tempstr varchar2(2000);

endstr number;

begin

targetstr := '1788987565327、768374872394903、21437238740213483874629、23412341234252345';

maxposition := 0;

select LENGTH(targetstr) into strlength  from dual;

for i in 1..strlength loop

select instr(str,'、',1,i) into position from (select targetstr as str from dual);

--dbms_output.PUT_LINE(position);

if position > 0 then

if maxposition = 0 then

select substr(str,0,instr(str,'、',1,1)-1) into retrunstr from (select targetstr as str from dual);

select TO_NUMBER(substr(restr,-1)) into endstr from (select retrunstr as restr from dual);

if endstr = 3 or endstr = 5 or endstr = 7 then

endstr := endstr + 1;

elsif endstr = 9 then

endstr := 0;

end if;

select substr(str,0,instr(str,'、',1,1)-2)||TO_CHAR(endstr) into retrunstr from (select targetstr as str from dual);

elsif maxposition < position then

select substr(str,instr(str,'、',1,i-1)+1,instr(str,'、',1,i)-instr(str,'、',1,i-1)-1) into tempstr from (select targetstr as str from dual);

select TO_NUMBER(substr(restr,-1)) into endstr from (select tempstr as restr from dual);

if endstr = 3 or endstr = 5 or endstr = 7 then

endstr := endstr + 1;

elsif endstr = 9 then

endstr := 0;

end if;

select substr(str,1,length(str)-1) ||TO_CHAR(endstr) into tempstr from (select tempstr as str from dual);

retrunstr := retrunstr || '、'|| tempstr;

end if;

maxposition := position;

else

if maxposition > position then

--特别处理最后一段

tempstr := '';

select substr(str,maxposition-length(str)) into tempstr from (select targetstr as str from dual);

select TO_NUMBER(substr(restr,-1)) into endstr from (select tempstr as restr from dual);

if endstr = 3 or endstr = 5 or endstr = 7 then

endstr := endstr + 1;

elsif endstr = 9 then

endstr := 0;

end if;

select substr(str,1,length(str)-1) ||TO_CHAR(endstr) into tempstr from (select tempstr as str from dual);

retrunstr := retrunstr || '、'|| tempstr;

end if;

exit;

end if;

end loop;

dbms_output.PUT_LINE(retrunstr);

end;

运行结果如下：

原字符串：1788987565327、768374872394903、21437238740213483874629、23412341234252345

14:03:38  **** SCRIPT STARTED: 02-Apr-2015 14:03:38 ****

14:03:38  declare

14:03:38      targetstr varchar2(2000);

14:03:38  ...

14:03:39  PL/SQL block executed

1788987565328、768374872394904、21437238740213483874620、23412341234252346

14:03:39  **** SCRIPT ENDED 02-Apr-2015 14:03:39 ****

14:03:39  End Script Execution