Shulou(Shulou.com)06/01 Report--

Today, someone asked a question about the interception and splicing of Oracle strings. He asked me to help him write the SQL. After reading the description of the problem, I tried to solve it. Using the lunch break to realize the function, the problem does not seem difficult, but the train of thought must be clear, otherwise it will be messed up. The key is to use a large number of Oracle substr and instr functions, and the following questions and scripts are posted:

Problem: the values of one field in sql are: 1788987565327, 768374872394903, 21437238740213483874629, 23412341234252345. The number of digits and Mantissa of each group of digits in the interval of the ton sign is uncertain. Now we should add 1 to the Mantissa of the digits before the ton sign in the previous field, that is, 7, 3, 9, 5, respectively. Become 8, 4, 0, 6 output to 1788987565328, 76837487239904, 214372340213483874620, 234123412232346. Notice that the third number, after adding 1 from 9, is output to 0 instead of 10.

Script:

Declare targetstr varchar2 (2000); strlength number; position number; maxposition number; retrunstr varchar2 (2000); tempstr varchar2 (2000); endstr number;begin targetstr: = '1788987565327, 768374872394903, 21437238740213483874629,2341234252345mm; maxposition: = 0; select LENGTH (targetstr) into strlength from dual; for i in 1..strlength loop select instr (str,',', 1 Magi) 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,',', 1mae 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 endstr 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-Special treatment for the last paragraph 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

The running results 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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.