LIKE Operator : Pattern-matching Conditions

The pattern-matching conditions compare character data.

LIKE Condition

* 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. 

* LIKE calculates strings using characters as defined by the input character set. 

* LIKEC uses Unicode complete characters. LIKE2 uses UCS2 code points. LIKE4 uses UCS4 code points.

The pattern can contain special pattern-matching characters:

• An underscore (_) in the pattern matches exactly one character (as opposed to one byte in a multibyte character set) in the value.

• A percent sign (%) in the pattern can match zero or more characters (as opposed to bytes in a multibyte character set) in the value. The pattern '%' cannot match a null.

You can include the actual characters % or _ in the pattern by using the ESCAPE clause, which identifies the escape character. If the escape character precedes the character % or _ in the pattern, then Oracle interprets this character literally in the pattern rather than as a special pattern-matching character. 

You can also search for the escape character itself by repeating it. For example, if @ is the escape character, then you can use @@ to search for @.

Note:
Only ASCII-equivalent underscore (_) and percent (%) characters are recognized as pattern-matching characters. Their full-width variants, present in East Asian character sets and in Unicode, are treated as normal characters.

To process the LIKE conditions, Oracle divides the pattern into subpatterns consisting of one or two characters each. The two-character subpatterns begin with the escape character and the other character is %, or _, or the escape character.

Let P1, P2, ..., Pn be these subpatterns. The like condition is true if there is a way to partition the search value into substrings S1, S2, ..., Sn so that for all i between 1 and n:

• If Pi  is _, then Si  is a single character.
• If Pi  is %, then Si  is any string.
• If Pi  is two characters beginning with an escape character, then Si  is the second character of Pi .
• Otherwise, Pi = Si.

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%'
 ORDER BY salary;

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%'
 ORDER BY salary;

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
 ORDER BY salary;

Collation and Case Sensitivity

The LIKE condition is collation-sensitive.

Oracle Database compares the subpattern Pi to the substring Si in the processing algorithm above using the collation determined from the derived collations of char1 and char2. If this collation is case-insensitive, the pattern-matching is case-insensitive as well.

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.