Replacing like with instr in Oracle databases

The following articles mainly introduce how to replace like with instr in Oracle databases. Suppose there is 11 million data in the table. In many cases, we perform String Matching on it, in the practical application of SQL statements, we generally use like to achieve our search goal.

However, the actual test shows that the like efficiency is quite different from the instr function. The following are some test results:

 
 

  
  
SQL> set timing on
  
  
SQL> select count (*) from t where instr (title, 'manual')> 0;
  
  
COUNT (*)
  
  
----
  
  
65881
  
  
Elapsed: 00:00:11. 04
  
  
SQL> select count (*) from t where title like '% manual % ';
  
  
COUNT (*)
  
  
----
  
  
65881
  
  
Elapsed: 00:00:31. 47
  
  
SQL> select count (*) from t where instr (title, 'manual') = 0;
  
  
COUNT (*)
  
  
----
  
  
11554580
  
  
Elapsed: 00:00:11. 31
  
  
SQL> select count (*) from t where title not like '% manual % ';
  
  
COUNT (*)
  
  
----
  
  
11554580
 
 

Note:

Instr (title, 'manual')> 0 is equivalent to like

Instr (title, 'manual') = 0 is equivalent to not like

The above content is the description of replacing like with instr for the Oracle database, hoping to provide you with some help in this regard.

Edit recommendations]