Keywords: SQL | NOT LIKE | ORA-00936 | Oracle | Pattern Matching
Abstract: This article provides an in-depth analysis of common misuses of the NOT LIKE operator in SQL queries, particularly focusing on the causes of Oracle's ORA-00936 error. Through concrete examples, it demonstrates correct syntax structures, explains the usage rules of AND connectors in WHERE clauses, and offers comprehensive solutions. The article also extends the discussion to advanced applications of LIKE and NOT LIKE operators, including case sensitivity and complex pattern matching scenarios.
Syntax Analysis of SQL NOT LIKE Operator
The NOT LIKE operator in SQL queries is used to filter data that does not match specific patterns. However, incorrect syntax usage can lead to database errors, such as Oracle's ORA-00936: missing expression error. This error typically stems from incomplete conditional expressions in the WHERE clause.
Common Error Case Analysis
Consider the following erroneous query example:
SELECT * FROM transactions WHERE id NOT LIKE '1%' AND NOT LIKE '2%'This query omits the field name id in the second NOT LIKE condition, causing Oracle to fail in recognizing a complete expression. In SQL syntax, the AND connector must join two complete conditional expressions, each containing a field name, operator, and value.
Correct Syntax Implementation
The corrected query should be:
SELECT * FROM transactions WHERE id NOT LIKE '1%' AND id NOT LIKE '2%'This approach explicitly specifies the field to which each NOT LIKE condition applies, ensuring syntactic completeness. The query's logic is: return all records from the transactions table where the id field does not start with '1' and does not start with '2'.
Advanced Applications of LIKE and NOT LIKE Operators
Referencing relevant technical discussions, LIKE and NOT LIKE operators have extensive applications in complex pattern matching. For instance, in scenarios requiring the inclusion of specific character sequences while excluding particular words, multiple conditions can be combined.
Consider an example searching for strings containing 'an' but excluding 'and':
SELECT * FROM table_name WHERE column_name LIKE '%an%' AND column_name NOT LIKE '%and%'This combination ensures result precision but requires attention to case sensitivity issues. In case-sensitive databases, standardization using UPPER() or LOWER() functions may be necessary.
Boundary Handling in Pattern Matching
In more complex pattern matching scenarios, word boundary considerations are essential. Simple NOT LIKE '%and%' might inadvertently exclude words containing 'and' as a substring, such as 'hand' or 'Andy'.
A more precise solution involves using boundary identifiers:
SELECT * FROM table_name WHERE column_name LIKE '%an%' AND '.' + column_name + '.' NOT LIKE '%[^a-z]and[^a-z]%'This method simulates word boundaries by adding special characters before and after the string, ensuring only independent 'and' words are excluded without affecting other words containing 'and' as a substring.
Practical Application Recommendations
In actual development, it is recommended to:
- Always ensure each condition in the WHERE clause is a complete expression
- Explicitly specify the field for each NOT LIKE condition when combining multiple conditions
- Consider database case sensitivity settings
- Use more precise boundary handling methods for complex pattern matching requirements
- Thoroughly test various edge cases in production environments
By following these best practices, common syntax errors can be avoided, and more accurate data filtering can be achieved.