Oracle string interception and splicing applications

Source: Internet
Author: User

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.