Instr replaces not like

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

Instr (title, 'manual') = 1 is equivalent to title like 'manual' %'

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

 

Special usage:

Select ID, name from users where instr ('1970 3', ID)> 0;
It is equivalent
Select ID, name from users where id = 101914 or ID = 104703;

 

In general, the following two methods are used to perform a fuzzy query on the name field of the TB table in the Oracle database:

1. Select * from TB where name like '% XX % ';
2. Select * from TB where instr (name, 'xx')> 0;

If no index is added to the name field, the efficiency is similar and there is basically no difference between the two.

To improve efficiency, we can add a non-unique index to the name field:
Create index idx_tb_name on TB (name );

In this way, use

Select * from TB where instr (name, 'xx')> 0;

The efficiency of such statement query can be improved a lot. The larger the table data volume, the larger the difference between the two. However, you must also consider the impact of the DML Statement on the re-sorting of index data after the name field is indexed.

From: http://www.blogjava.net/rabbit/archive/2013/01/21/394510.html

Instr replaces not like