Usage of instr and substr in Oracle

Instr usage in Oracle:
The format of the instr method is
Instr (source string, the string to be searched, starting from the nth character, to find the nth matching serial number)
Returns the location found. If not, 0 is returned.
For example, in instr ('upgrade floor ', 'or', 3, 2), the source string is 'upgrade floor', and the string is 'or ', search for "or" from the third character, and take the location of the third character and the second matching item.

The default search order is left to right. When the start position is negative, search from the right.

Therefore, the result of select instr ('upgrade floor ',' or ',-1, 1) "AAA" from dual is:

Instring
------
14

Usage of the substr function in Oracle:
Obtain the string substr (string, start_position, [length]) with the specified start position and length in the string.
For example:
Substr ('this is a test', 6, 2) wowould return 'is'
Substr ('this is a test', 6) wocould return 'is a Test'
Substr ('techonthenet ',-3, 3) wocould return 'net'
Substr ('techonthenet ',-6, 3) wocould return 'the'
 
Select substr ('thisatest ',-4, 2) value from dual

Comprehensive application:
Select instr ('ate floor ',' or ',-1, 1) "instring" from dual
-- Instr (source string, target string, start position, matching serial number)
Select instr ('ate 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 by Administrator
Declare
-- Local variables here
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 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 by Administrator
Declare
-- Local variables here
I integer;
T varchar2 (2000 );
S varchar2 (2000 );
Begin
-- Test statements here
-- Historical Status
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 ));
/* Insert into t_history_responsestatus
(Historyid, responsestatusid, stamp, call_letter)
Values
(Ihistoryid, S, istamp, icall_letter );
*/-- Commit;
End if;
T: = substr (T, instr (T, ';') + 1 );
End loop;
End if;
End;