LIKE Predicate

A LIKE predicate evaluates to TRUE if the source contains a given pattern. The LIKE predicate matches a portion of one character value to another by searching the source for the pattern specified.

SQL syntax

Source [NOT] LIKE Pattern 
 [ESCAPE {'EscapeChar' | {? | :DynamicParameter} }]

The syntax for Pattern is as follows:

Expression [ || Expression ] [ ... ]

Parameters

Parameter	Description
`Source`	This source is searched for all occurrences of the pattern. The source may be an expression, column, character string resulting from a function, or any combination of these that results in a character string used for the source on which the pattern is matched. The source can be a `CHAR`, `VARCHAR2`, `NCHAR`, `NVARCHAR2`, `CLOB`, or `NCLOB`. See "Expressions" for information on expressions. See "Pattern Matching for Strings of NCHAR, NVARCHAR2, and NCLOB Data Types" for information on searching within a national character string within `NCHAR`, `NVARCHAR`, or `NCLOB`.
`Pattern`	Describes a character pattern that you are searching for in the source with one or more expressions. The data type of the pattern should be a character string data type, such as `CHAR`, `VARCHAR2`, `NCHAR`, `NVARCHAR2`, `CLOB`, or `NCLOB`. Multiple expressions may be concatenated to form the character string used for the pattern. The pattern consists of characters including digits and special characters. For example, `NAME LIKE 'Annie'` evaluates to `TRUE` only for a name of Annie with no spaces. You can also use the predicate to test for a partial match by using one or more of the following symbols: The symbol `_` represents any single character. For example: `BOB` and `TOM` both satisfy the predicate `NAME LIKE '_O_'`. The symbol `%` represents any string of zero or more characters. For example: `MARIE` and `RENATE` both satisfy the predicate `NAME LIKE '%A%'`. You can use the `_` or `%` symbols multiple times and in any combination in a pattern. However, you cannot use the symbols literally within a pattern unless you use the `ESCAPE` clause and precede the symbols with the escape character, described by the `EscapeChar` parameter.
`Expression`	Any expression included in the pattern may be a column, a dynamic parameter, or the result of a function that evaluates to a character string. See "Expression Specification" for information on expressions.
`EscapeChar`	Describes an optional escape character which can be used to interpret the symbols `_` and `%` literally in the pattern. The escape character must be a single character. When it appears in the pattern, it must be followed by the escape character itself, the `_` symbol or the `%` symbol. Each such pair represents a single literal occurrence of the second character in the pattern. The escape character is always case sensitive. The escape character cannot be `_` or `%`.
? `DynamicParameter`	Indicates a dynamic parameter in a prepared SQL statement. The parameter value is supplied when the statement is executed.

Description

As long as no escape character is specified, the _ or % symbols in the pattern act as wild card characters. If an escape character is specified, the wild card or escape character that follows is treated literally. If the character following an escape character is not a wild card or the escape character, an error results.
When providing a combination of expressions, columns, character strings, dynamic parameters, or function results to form the pattern, you can concatenate items together using the || operator to form the final pattern.
Case is significant in all conditions comparing character expressions that use the LIKE predicate.
If the value of the expression, the pattern, or the escape character is NULL, the LIKE predicate evaluates to NULL.
The LIKE predicate may be slower when used on a multibyte character set.
See "Pattern Matching for Strings of NCHAR, NVARCHAR2, and NCLOB Data Types" for more information on searching within a national character string within NCHAR, NVARCHAR, or NCLOB.

Examples

Find each employee whose last name begins with 'Sm'.

Command> SELECT employee_id, last_name,first_name FROM employees
         WHERE last_name LIKE 'Sm%'
         ORDER BY employee_id,last_name,first_name;
< 159, Smith, Lindsey >
< 171, Smith, William >
2 rows found.

Find each employee whose last name begins with 'SM'. This query returns no results because there are no employees whose last_name begins with upper case 'SM'.

Command> SELECT employee_id, last_name,first_name from employees
         WHERE last_name LIKE 'SM%' 
         ORDER BY employee_id,last_name,first_name;
0 rows found.

However, by upper casing the source value of the last name column, you can find all names that begin with 'SM'.

Command> SELECT employee_id, last_name, first_name FROM employees 
         WHERE UPPER(last_name) LIKE ('SM%');
< 159, Smith, Lindsey >
< 171, Smith, William >
2 rows found.

Use a dynamic parameter denoted by ? to find each employee whose last name begins with 'Sm' at execution time.

Command> SELECT employee_id, last_name,first_name FROM employees
         WHERE last_name like ?
         ORDER BY employee_id,last_name,first_name;
 
Type '?' for help on entering parameter values.
Type '*' to end prompting and abort the command.
Type '-' to leave the parameter unbound.
Type '/;' to leave the remaining parameters unbound and execute the command.
 
Enter Parameter 1 '_QMARK_1' (VARCHAR2) > 'Sm%'
< 159, Smith, Lindsey >
< 171, Smith, William >
2 rows found.

Use a bind variable denoted by :a to find each employee whose last name begins with 'Sm' at execution time.

Command> SELECT employee_id, last_name,first_name FROM employees 
         WHERE last_name LIKE :a 
         ORDER BY employee_id,last_name,first_name;
 
Type '?' for help on entering parameter values.
Type '*' to end prompting and abort the command.
Type '-' to leave the parameter unbound.
Type '/;' to leave the remaining parameters unbound and execute the command.
 
Enter Parameter 1 'A' (VARCHAR2) > 'Sm%'
< 159, Smith, Lindsey >
< 171, Smith, William >
2 rows found.

For each employee whose last name begins with 'Smit', find the last name of the manager. Display the first name and last name of the employee and the last name of the manager.

Command> SELECT e1.first_name || ' ' || e1.last_name||' works for '||e2.last_name
         FROM employees e1, employees e2
         WHERE e1.manager_id = e2.employee_id
         AND e1.last_name like 'Smit';
< Lindsey Smith works for Partners >
< William Smith works for Cambrault >
2 rows found.

This query pattern references the last_name column as the pattern for which to search:

Command> SELECT e1.first_name || ' ' || e1.last_name||' works for ' || 
                e2.last_name
         FROM employees e1, employees e2
         WHERE e1.manager_id = e2.employee_id AND 'Smith' like e1.last_name;
< Lindsey Smith works for Partners >
< William Smith works for Cambrault >
2 rows found.

The pattern can be a column or the result of a function. The following uses the UPPER function on both the source last_name column as well as the 'ma' search string for which you are searching:

Command> SELECT last_name, first_name FROM employees
         WHERE UPPER(last_name) LIKE UPPER('ma%');
< Markle, Steven >
< Marlow, James >
< Mallin, Jason >
< Matos, Randall >
< Marvins, Mattea >
< Mavris, Susan >
6 rows found.

The following query demonstrates using a dynamic parameter to request the pattern.

Command> SELECT first_name || ' ' || last_name 
         FROM employees WHERE last_name like ?;
 
Type '?' for help on entering parameter values.
Type '*' to end prompting and abort the command.
Type '-' to leave the parameter unbound.
Type '/;' to leave the remaining parameters unbound and execute the command.
 
Enter Parameter 1 '_QMARK_1' (VARCHAR2) > 'W%'
< Matthew Weiss >
< Alana Walsh >
< Jennifer Whalen >
3 rows found.

The following query demonstrates combining a character string with a dynamic parameter in the pattern.

Command> SELECT first_name || ' ' || last_name 
         FROM employees WHERE last_name like 'W' || ?;
 
Type '?' for help on entering parameter values.
Type '*' to end prompting and abort the command.
Type '-' to leave the parameter unbound.
Type '/;' to leave the remaining parameters unbound and execute the command.
 
Enter Parameter 1 '_QMARK_1' (VARCHAR2) > '%'
< Matthew Weiss >
< Alana Walsh >
< Jennifer Whalen >
3 rows found.

Pattern Matching for Strings of NCHAR, NVARCHAR2, and NCLOB Data Types

The LIKE predicate can be used for pattern matching for strings of type NCHAR, NVARCHAR2, and NCLOB. The pattern matching characters are:

Character	Description
`U+005F SPACING UNDERSCORE`	Represents any single Unicode character.
`U+0025 PERCENT SIGN`	Represents any string of zero or more Unicode characters.

Description

The escape character is similarly supported as a single Unicode character or parameter.
The types of the LIKE operands can be any combination of character types.
Case-insensitive and accent-insensitive NLS_SORT is supported with the LIKE predicate.

Examples

In these examples, the Unicode character U+0021 EXCLAMATION MARK is being used to escape the Unicode character U+005F SPACING UNDERSCORE. Unicode character U+0025 PERCENT SIGN is not escaped, and assumes its pattern matching meaning.

VendorName is an NCHAR or NVARCHAR2 column.

SELECT VendorName FROM Purchasing.Vendors
WHERE VendorName LIKE N'ACME!_%' ESCAPE N'!';

This example is equivalent:

SELECT VendorName FROM Purchasing.Vendors
WHERE VendorName LIKE N'ACME!\u005F\u0025' ESCAPE N'!';