Oracle InStr function usage and (Oracle replaces like with InStr)

The Oracle InStr function appeared in the recent project, so I looked up information about it on the Internet.

Usage of InStr and SUBSTR in Oracle

Usage of instr in Oracle:
The format of the InStr method is
INSTR (source string, the string to find, starting with the first few characters, to find the ordinal number of the first match)
Returns the location found and returns 0 if it is not found.
For example: INSTR (' CORPORATE floor ', ' or ', 3, 2), the source string is ' CORPORATE floor ', find ' or ' in the string, find "or" from the third character position, and take the position of the 2nd occurrence after the third word.

The default lookup order is left to right. When the starting position is negative, start looking from the right.

So select INSTR (' CORPORATE floor ', ' OR ',-1, 1) the "AAA" Fromdual display results are

Instring
——————
14

Oracle's use of the SUBSTR function:
Gets the string substr (String, start_position, [length]) that specifies the starting position and length in the string
such as:
substr (' This is a test ', 6,2) would retu RN ' is '
substr (' This is a test ', 6) would return ' is a test '
substr (' techonthenet ', -3,3) /c27>would return ' Net '
substr (' techonthenet ', -6,3) /c35>would return ' the '

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

Comprehensive application:
Select INSTR (' CORPORATE floor ', ' OR ',-1, 1) "Instring" fromdual
--instr (source string, target string, starting position, matching 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 here
TVARCHAR2 (2000);
SVARCHAR2 (2000);
NUM INTEGER;
IINTEGER;
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 2008-9-26 by ADMINISTRATOR
Declare
--Local variables here
I integer;
TVARCHAR2 (2000);
SVARCHAR2 (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 | | ', ';
Whilelength (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);
Endloop;
END IF;
End

Oracle InStr functions

For the InStr function, we often use this: find the location of the specified substring from a string. For example:

Sql> Select InStr (' Oracle ', ' or ') position from dual;

POSITION

----------

1

Starting at the first position of the string ' Oracle ', look backwards to find the first occurrence of the substring ' or '.

In fact, InStr a total of 4 parameters, in the Format "InStr (String, substring, startposition, occurrence)". The following search can be implemented for substrings:

1. Search for substrings starting at the specified location

2. Specify the location of the substring to search for the first occurrence

3. Forward search from behind

--1. Starting with the 3rd character search

Sql> Select InStr (' Oracleor ', ' or ', 3) position from dual;

POSITION

----------

7

--2. Starting with the 1th character, search the location of the 2nd occurrence of the substring

Sql> Select InStr (' Oracleor ', ' or ', 1, 2) position from dual;

POSITION

----------

7

--3. Start with the last 1th character and search for the 1th occurrence of a substring

Sql> Select InStr (' Oracleor ', ' or ',-1, 1) position from dual;

POSITION

----------

7

--3. Start with the last 1th character and search for the 2nd occurrence of a substring

Sql> Select InStr (' Oracleor ', ' or ',-1, 2) position from dual;

POSITION

----------

1

Oracle replaces like with InStr

There are nearly 1 million data in the table, many times we want to do string matching, in the SQL statement, we usually use like to achieve the goal of our search. However, the actual test shows that the efficiency of like is quite different from the InStr function. Here are some test results:

Sql> Set Timing on
Sql> Select COUNT (*) from T where InStr (title, ' Oracle ') >0;

COUNT (*)
———-
5478

elapsed:00:00:11.04
Sql> Select COUNT (*) from T where the title like '%oracle% ';

COUNT (*)
———-
5478

elapsed:00:00:31.47
Sql> Select COUNT (*) from T where InStr (title, ' Oracle ') = 0;

COUNT (*)
———-
994530

elapsed:00:00:11.31
Sql> Select COUNT (*) from T where title is not like '%oracle% ';

COUNT (*)
———-
994530

Note:

InStr (Title, ' Oracle ') >0 equivalent to Like

InStr (Title, ' Oracle ') =0 equivalent to not

Oracle InStr function usage and (Oracle replaces like with InStr)