Use of InStr and substr in Oracle ____oracle

Source: Internet
Author: User

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);
/* 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


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.