Page 197 - DCAP310_INTRODUCTION_TO_ARTIFICIAL_INTELLIGENCE_AND_EXPERT_SYSTEMS

Unit 10: Matching Techniques

Let P , P , ..., P be these subpatterns. The like condition is true if there is a way to partition the Notes
1 2 n
search value into substrings S , S , ..., S so that for all i between 1 and n:
1 2 n
If P is _, then S is a single character.
i i
If P is %, then S is any string.
i i
If P is two characters beginning with an escape character, then S is the second character of
i i
P .
i
Otherwise, P = S .
i i
With the LIKE conditions, you can compare a value to a pattern rather than to a constant. The
pattern must appear after the LIKE keyword. For example, you can issue the following query to
find the salaries of all employees with names beginning with R:
SELECT salary
FROM employees
WHERE last_name LIKE ‘R%’;
The following query uses the = operator, rather than the LIKE condition, to find the salaries of
all employees with the name ‘R%’:
SELECT salary
FROM employees
WHERE last_name = ‘R%’;
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;

Case Sensitivity

Case is significant in all conditions comparing character expressions that use the LIKE condition
and the equality (=) operators. You can perform case or accent insensitive LIKE searches by
setting the NLS_SORT and the NLS_COMP session parameters.

Pattern Matching on Indexed Columns

When you use LIKE to search an indexed column for a pattern, Oracle can use the index to
improve performance of a query if the leading character in the pattern is not % or _. In this case,
Oracle can scan the index by this leading character. If the first character in the pattern is % or _,
then the index cannot improve performance because Oracle cannot scan the index.

LIKE Condition: General Examples

This condition is true for all last_name values beginning with Ma:
last_name LIKE ‘Ma%’
All of these last_name values make the condition true:
Mallin, Markle, Marlow, Marvins, Marvis, Matos
Case is significant, so last_name values beginning with MA, ma, and mA make the condition
false.
Consider this condition:
last_name LIKE ‘SMITH_’

LOVELY PROFESSIONAL UNIVERSITY 191