|Oracle9i SQL Reference
Release 2 (9.2)
Part Number A96540-01
Conditions, 10 of 13
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 codepoints.
LIKE4 uses USC4 codepoints.
In this syntax:
char1is a character expression, such as a character column, called the search value.
char2is a character expression, usually a literal, called the pattern.
esc_charis a character expression, usually a literal, called the escape character.
esc_char is not specified, then there is no default escape character. If any of
esc_char is null, then the result is unknown. Otherwise, the escape character, if specified, must be a character string of length 1.
All of the character expressions (
esc_char) can be of any of the datatypes
NVARCHAR2. If they differ, then Oracle converts all of them to the datatype of
The pattern can contain the special pattern-matching characters:
To search for the characters % and _, precede them by the escape character. For example, if the escape character is @, then you can use @% to search for %, and @_ to search for _.
To search for the escape character, repeat it. For example, if @ is the escape character, then you can use @@ to search for @.
In the pattern, the escape character should be followed by one of %, _, or the escape character itself.
Table 5-11 describes the
|Type of Condition||Operation||Example|
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:
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':
The following query uses the = operator, rather than the
LIKE condition, to find the salaries of all employees with the 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
Patterns typically use special characters that Oracle matches with different characters in the value:
Case is significant in all conditions comparing character expressions including the
LIKE condition and the equality (=) operators. You can use the
UPPER function to perform a case-insensitive match, as in this condition:
When you use
LIKE to search an indexed column for a pattern, Oracle can use the index to improve the statement's performance 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 the query's performance because Oracle cannot scan the index.
This condition is true for all
last_name values beginning with "Ma":
All of these
last_name values make the condition true:
Case is significant, so
last_name values beginning with "MA", "ma", and "mA" make the condition false.
Consider this condition:
This condition is true for these
This condition is false for '
SMITH', since the special character "_" must match exactly one character of the
You can include the actual characters "%" or "_" in the pattern by using the
ESCAPE clause, which identifies the escape character. If the escape character appears in the pattern before the character "%" or "_" then Oracle interprets this character literally in the pattern, rather than as a special pattern matching character.
To search for employees with the pattern 'A_B' in their name:
ESCAPE clause identifies the backslash (\) as the escape character. In the pattern, the escape character precedes the underscore (_). This causes Oracle to interpret the underscore literally, rather than as a special pattern matching character.
If a pattern does not contain the "%" character, then the condition can be true only if both operands have the same length. Consider the definition of this table and the values inserted into it:
CREATE TABLE ducks (f CHAR(6), v VARCHAR2(6)); INSERT INTO ducks VALUES ('DUCK', 'DUCK'); SELECT '*'||f||'*' "char", '*'||v||'*' "varchar" FROM ducks; char varchar -------- -------- *DUCK * *DUCK*
Because Oracle blank-pads
CHAR values, the value of
f is blank-padded to 6 bytes.
v is not blank-padded and has length 4.