Oracle InStr Functions (Oracle replaces like with InStr) __ static functions

Oracle InStr functions

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

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

POSITION

----------

1

Starts at the first position of the string ' Oracle ', looking backwards for the first occurrence of the substring ' or '.

In fact, InStr has 4 parameters, the format is "InStr (string, substring, startposition, occurrence)". You can implement the following search for substrings:

1. Search substring starting at specified location

2. Specify the location of the substring that appears the first occurrence of the search

3. Search from Back forward

--1. Start the search from the 3rd character

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

POSITION

----------

7

--2. Start with the 1th character and search for 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 the 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 the substring

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

POSITION

----------

1

Oracle replaces like with InStr

There are nearly 1 million of data in the table, many times we have to do string matching, in SQL statements, we usually use like to achieve our search goals. But 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 isn't like '%oracle% ';

COUNT (*)
———-
994530

Note:

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

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