Oracle字符串截取和拼接應(yīng)用
今天有人問了個關(guān)于Oracle字符串截取和拼接的問題，讓我?guī)退麑懗鯯QL，看了下問題描述還比較清晰就試著解決下，利用午休時間把功能實現(xiàn)了，問題看似不難，但思路一定要清晰，不然就亂了，關(guān)鍵大量應(yīng)用了Oracle的substr 和instr函數(shù)，下面貼出問題和腳本：
成都創(chuàng)新互聯(lián)公司專業(yè)成都網(wǎng)站制作、成都網(wǎng)站設(shè)計，集網(wǎng)站策劃、網(wǎng)站設(shè)計、網(wǎng)站制作于一體，網(wǎng)站seo、網(wǎng)站優(yōu)化、網(wǎng)站營銷、軟文營銷等專業(yè)人才根據(jù)搜索規(guī)律編程設(shè)計，讓網(wǎng)站在運行后，在搜索中有好的表現(xiàn)，專業(yè)設(shè)計制作為您帶來效益的網(wǎng)站！讓網(wǎng)站建設(shè)為您創(chuàng)造效益。
問題：sql中一個字段值為：1788987565327、768374872394903、21437238740213483874629、23412341234252345。其中頓號間隔的每一組數(shù)字位數(shù)和尾數(shù)不定，現(xiàn)在要使前面這個字段值中頓號前的數(shù)字尾數(shù)即7、3、9、5都分別加1，變成8、4、0、6輸出成1788987565328、768374872394904、21437238740213483874620、23412341234252346。注意其中第三個數(shù)，從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;
運行結(jié)果如下：
原字符串：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
本文名稱：Oracle字符串截取和拼接應(yīng)用
文章起源：http://m.jiaotiyi.com/article/peijdj.html