Page 196 - DCAP310_INTRODUCTION_TO_ARTIFICIAL_INTELLIGENCE_AND_EXPERT_SYSTEMS

Introduction to Artificial Intelligence & Expert Systems

Notes In this syntax:
char1 is a character expression, such as a character column, called the search value.
char2 is a character expression, usually a literal, called the pattern.
esc_char is a character expression, usually a literal, called the escape character.

The LIKE condition is the best choice in almost all situations. Use the following guidelines to
determine whether any of the variations would be helpful in your environment:
Use LIKE2 to process strings using UCS-2 semantics. LIKE2 treats a Unicode supplementary
character as two characters.
Use LIKE4 to process strings using UCS-4 semantics. LIKE4 treats a Unicode supplementary
character as one character.
Use LIKEC to process strings using Unicode complete character semantics. LIKEC treats a
composite character as one character.

If esc_char is not specified, then there is no default escape character. If any of char1, char2, or
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 (char1, char2, and esc_char) can be of any of the datatypes CHAR,
VARCHAR2, NCHAR, or NVARCHAR2. If they differ, then Oracle converts all of them to the
datatype of char1.
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.

Did u know? We 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 @.
The table shown below describes the LIKE conditions.

Type of Operation Example
Condition
x [NOT] TRUE if x does [not] match the pattern y. Within y, the SELECT last_name
LIKE y character % matches any string of zero or more characters FROM employees
[ESCAPE except null. The character _ matches any single character. WHERE last_name
'z'] LIKE '%A\_B%'
Any character can follow ESCAPE except percent (%) and ESCAPE '\';
underbar (_). A wildcard character is treated as a literal if
preceded by the escape character.

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.

190 LOVELY PROFESSIONAL UNIVERSITY