How to query in oracle that does not contain specified characters

Method: 1. Use the statement "select * from table name where order_no not like characters" to query; 2. Use the statement "select * from table name where not regexp_like (order_no, characters)" to query.

The operating environment of this tutorial: Windows 10 system, Oracle 11g version, Dell G3 computer.

How to query in oracle that does not contain specified characters

I encountered a requirement during the development process. The data column that the user wants to extract does not contain strings of YF, ZF, and JD.

Method 1:

select * from table  where  order_no not like '%YF%' and order_no not like '%ZF' and order_no not like '%JD%'

I feel that method 1 is a bit stupid. I thought that REGEXP_LIKE can contain multiple REGEXP_LIKEs. Adding not in front can achieve non-containing functions. The method is as follows:

Method 2:

select * from table where not regexp_like(order_no,'YF|ZF|JD')

Both methods can be implemented, but there are efficiency issues. After querying 110,000 data for two months, method 1 took 18 seconds, method 2 took 15 seconds, and method 1 was tested three times in a row. The method is 2 to 3 seconds faster. If the amount of data is large, it is recommended to use method 1!

Recommended tutorial: "Oracle Video Tutorial"

The above is the detailed content of How to query in oracle that does not contain specified characters. For more information, please follow other related articles on the PHP Chinese website!