Question

为sql一字段值中每个特定字符前的最后一个数字加1怎么处理？

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

Answer 1

你这必须得采纳我的了，中午都没休息，帮你写了，你运行看看结果：
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;

运行结果如下：
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

Answer 2

做一个函数来处理：
1、Split Token，根据分隔符【、】，取出数字串
2、每个Token取最后一位字符并转换成数字CAST(Right(Token, 1) AS Int)
3、+ 1 并mod 10
4、转换为字符，并替换Token的最后一位字符
5、重新组装成字符串