LIKE Predicate (like_predicate) 

A LIKE predicate is used to search for character strings that have a particular pattern. This pattern can be a certain character string or any sequence of characters (whose length may or may not be known).

Syntax

<like_predicate> ::= <expression> [NOT] LIKE <like_expression> [ESCAPE <expression>]

<like_expression> ::= <expression> | '<pattern_element>...'

expression, pattern_element

Explanation

The expression in the like_expression must supply an alphanumeric value or a date or time value.

x NOT LIKE y has the same result as NOT(x LIKE y).

	Result of x LIKE y
x or y are NULL values	x LIKE y is undefined
x and y are non-NULL values	x LIKE y is either true or false
x can be split into substrings with the result that: A substring of x is a sequence of 0,1, or more contiguous characters, and each character of x belongs to exactly one substring. The number of substrings of x and y is identical. If the nth pattern element of y is a sequence of characters and the nth substring of x is a sequence of 0 or more characters.	x LIKE y is true

ESCAPE

An escape character (ESCAPE <expression>) must be used if a search is to be performed for an <underscore>, '%', or the hexadecimal value X'1E' or X'1F' in the LIKE predicate.

If ESCAPE <expression> is specified, the corresponding expression ( expression) must supply an alphanumeric value that consists of just one character. If this escape character is contained in the LIKE expression ( like_expression), the following character must be one of the special characters <underscore>, %, X’1E’, or X’1F’. This special character is then viewed as standing for itself.

Search for any character string with a minimum length of 1: LIKE '%_'
Search for a character string in which a fixed number of characters is known: LIKE'_c_'
Search for a character string with any number of characters, whereby the character string must contain an <underscore>: LIKE '%:_%'ESCAPE':'

Example table: customer

Customers whose name ends with 'FT':

SELECT name, city FROM customer
WHERE name LIKE '%FT'

NAME	CITY
DATASOFT	Dallas

 

Finding all customers whose names consist of six letters and begin with 'P':

SELECT name, firstname, city FROM customer
WHERE name LIKE 'P_ _ _ _ _'

NAME	FIRSTNAME	CITY
Porter	Jenny	New York
Porter	Martin	Los Angeles
Peters	Sally	Los Angeles
Porter	Michael	New York
Peters	Joseph	Los Angeles

 

Customers with a 'p' in their name from the second position:

SELECT name, city FROM customer
WHERE name LIKE '_%p%'

NAME	CITY
Randolph	Los Angeles