ILIKE condition in Oracle SQL
Using Case Sensitivity (alternative to ILIKE
)
LIKE
The LIKE
conditions specify a test involving pattern matching. Whereas the equality operator (=) exactly matches one character value to another, the LIKE
conditions match a portion of one character value to another by searching the first value for the pattern specified by the second.
The following query finds the salaries of all employees with the name ‘SM%’. Oracle interprets ‘SM%’ as a text literal, rather than as a pattern, because it precedes the LIKE
keyword:
SELECT salary
FROM employees
WHERE 'SM%' LIKE last_name;
ILIKE
There are noILIKE
condition in Oracle SQL, but you still can have alternative way to do it.
Using Case Sensitivity (alternative to ILIKE
)
Case is significant in all conditions comparing character expressions that the LIKE
condition and the equality (=) operators. You can use the UPPER
function to perform a case-insensitive match, as in this condition:
UPPER(last_name) LIKE 'SM%'
REF : https://docs.oracle.com/cd/B13789_01/server.101/b10759/conditions016.htm