Oracle InStr and like

Original: http://www.cnblogs.com/crazyjava/archive/2012/10/31/2748202.html

InStr (String1,string2[,start_position[,nth_appearence])

String1: To find in this string.

String2: The string to find in string1.

Start_position: The location to look for from string1. Optional, default is 1, positive numbers are retrieved from left to right, negative numbers are retrieved from right to left.

Nth_appearence: Find the first few occurrences of string2. Optional, default is 1, cannot be negative.

Note: If not found, returns 0.

For example:

Select InStr (' ABCD ', ' a ') from dual; --Return 1
Select InStr (' ABCD ', ' C ') from dual; --Return 3
Select InStr (' ABCD ', ' e ') from dual; --Return 0

This function can be used for fuzzy queries and to determine the inclusion relationship:

For example:

①select code, Name, dept, occupation from the staff where InStr (Code, ' 001 ') > 0;

Equivalent to

Select Code, Name, dept, occupation from the staff where code like '%001% ';

②select Ccn,mas_loc from Mas_loc where InStr (' Fh,fhh,fhm ', CCN) >0;

Equivalent to

Select Ccn,mas_loc from Mas_loc where CCN in (' FH ', ' FHH ', ' FHM ');

Another article:

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.

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

Note:

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

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

Oracle InStr and like