Use of InStr and substr in Oracle ____oracle

Usage of InStr and SUBSTR in Oracle
Usage of instr in Oracle:
The format of the InStr method is
INSTR (The source string, the string to find, starting with the first few characters, to find the ordinal number of matches)
Returns the location found, or 0 if it is not found.
For example: INSTR (' CORPORATE FLOOR ', ' or ', 3, 2), the source string is ' CORPORATE FLOOR ', look for ' or ' in the string, look for "or" from the third character position, and the position of the 2nd match after the third word.
The default lookup order is left to right. When the starting position is a negative number, start looking from the right.
So the result of the Select INSTR (' CORPORATE FLOOR ', ' or ',-1, 1) "AAA" from dual is
Instring
——————
14


The use of Oracle's SUBSTR function:
Gets the string substr (string, start_position, [length]) of the specified starting position and length in the string
Such as:
SUBSTR (' This are a test ', 6, 2) would return ' is '
SUBSTR (' This are a test ', 6) would return ' is a test '
substr (' techonthenet ', -3, 3) would return ' Net '
substr (' Techonthenet ',-6, 3) would return ' the '

Select substr (' Thisisatest ', -4, 2) value from dual


Comprehensive application:
Select INSTR (' CORPORATE FLOOR ', ' OR ',-1, 1) "Instring" from DUAL
--instr (source string, target string, start position, match ordinal)
Select INSTR (' CORPORATE FLOOR ', ' OR ', 3, 2) ' instring ' from DUAL
Select INSTR (' 32.8,63.5 ', ', ', 1, 1) ' instring ' from DUAL
Select SUBSTR (' 32.8,63.5 ', INSTR (' 32.8,63.5 ', ', ', 1, 1) +1) "Instring" from DUAL
Select SUBSTR (' 32.8,63.5 ', 1,instr (' 32.8,63.5 ', ', ', 1, 1)-1) "Instring" from DUAL
--CREATED on 2008-9-26 by ADMINISTRATOR
DECLARE
--Local VARIABLES
T VARCHAR2 (2000);
S VARCHAR2 (2000);
NUM INTEGER;
I INTEGER;
POS INTEGER;
BEGIN
--TEST statements here
T: = ' 12.3,23.0;45.6,54.2;32.8,63.5; ';
SELECT Length (t)-Length (REPLACE (t, '; ', ')) into the NUM from DUAL;
Dbms_output. Put_Line (' NUM: ' | | NUM);
POS: = 0;
For I in 1. NUM LOOP
Dbms_output. Put_Line (' I: ' | | I);
Dbms_output. Put_Line (' POS: ' | | POS);
Dbms_output. Put_Line (' = =: ' | | INSTR (T, '; ', 1, I));
Dbms_output. Put_Line (' INSTR: ' | | SUBSTR (T, POS + 1, INSTR (t, '; ', 1, I)-1));
POS: = INSTR (T, '; ', 1, I);
End LOOP;
End;


--Created on 2008-9-26 by ADMINISTRATOR
Declare
--Local variables
I integer;
T VARCHAR2 (2000);
S VARCHAR2 (2000);
Begin
--Test statements here
--Historical state
T: = ' 12.3,23.0;45.6,54.2;32.8,63.5; ';
IF (T is not NULL) and (LENGTH (T) > 0) THEN
--t: = T | | ',';
While LENGTH (T) > 0 loop
--istatusid: = 0;
S: = TRIM (SUBSTR (t, 1, INSTR (t, '; ')-1);
IF LENGTH (S) > 0 THEN
Dbms_output. Put_Line (' LAT: ' | | SUBSTR (' 32.8,63.5 ', 1,instr (' 32.8,63.5 ', ', ', 1, 1)-1);
Dbms_output. Put_Line (' LON: ' | | SUBSTR (' 32.8,63.5 ', INSTR (' 32.8,63.5 ', ', ', 1, 1) +1);
--COMMIT;
End IF;
T: = SUBSTR (T, INSTR (t, '; ') + 1);
End LOOP;
End IF;
End