Oracle string truncation and splicing applications
Today, someone asked me a question about Oracle string truncation and concatenation. I asked him to write an SQL statement. After reading the Problem description, I tried to solve it clearly, using the lunch break to implement the function seems to be difficult, but the idea must be clear. Otherwise, it will be messy. A large number of Oracle substr and instr functions are applied to the key. The following is a question and script:
Problem: the values of a field in SQL are 1788987565327, 768374872394903, 21437238740213483874629, and 23412341234252345. The number of digits and the number of tails in each set of intervals are not fixed. Now we need to add 1 to the number ending numbers before the number in the preceding field values, namely, 7, 3, 9, and 5 respectively, to 1788987565328, 768374872394904, 21437238740213483874620, 23412341234252346, and. Note 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: = '2014, 1788987565327, 768374872394903, 21437238740213483874629 ';
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
-- Special Handling of the last section
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 result is 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