今天有人问了个关于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
免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。