Oracle string interception and splicing applications

Today, someone asked a question about Oracle string interception and splicing, let me help him write SQL, read the problem description is relatively clear to try to solve, the use of lunch time to realize the function, the problem seems not difficult, but the idea must be clear, or chaos, The substr and InStr functions of Oracle are heavily applied, and the following issues and scripts are posted:

Problem: A field value in SQL is: 1788987565327, 768374872394903, 21437238740213483874629, 23412341234252345. Where the comma interval of the number of digits and the mantissa is indeterminate, now to the previous field value before the number of comma before the mantissa that is 7, 3, 9, 5 are added 1, 8, 4, 0, 6 output to 1788987565328, 768374872394904, 21437238740213483874620, 23412341234252346. Note that the third number, from 9 plus 1, outputs 0 instead of 10

Script:

DECLARE&NBSP;&NBSP;&NBSP;&NBSP;TARGETSTR&NBSP;VARCHAR2 (;    strlength number;)     position number;    maxposition number;   &NBSP;&NBSP;RETRUNSTR&NBSP;VARCHAR2 (&NBSP;&NBSP;&NBSP;&NBSP;TEMPSTR&NBSP;VARCHAR2);     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); &NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBsp; if position > 0 then              if maxposition = 0 then        &NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;SELECT&NBSP;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             &NBSP;&NBSP;&NBSP;&NBSP;SELECT&NBSP;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;        &NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;SELECT&NBSP;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                --special deal with the last paragraph                  tempstr :=  ';           &NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;SELECT&NBSP;SUBSTR (Str,maxposition-length (str)) &NBSP;INTO&NBSP;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;         &NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;SELECT&NBSP;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;

The results of the operation are as follows:

Original string: 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

This article is from the "Qytag (upspringing)" blog, so be sure to keep this source http://qytag.blog.51cto.com/6125308/1627680

Oracle string interception and splicing applications