LIKE

Retrieves rows where a string expression—typically a column—matches the specified pattern or, if qualified by ANY or ALL, set of patterns.

Retrieves rows where a string expression—typically a column—matches the specified pattern or, if qualified by ANY or ALL, set of patterns. Patterns can contain one or more wildcard characters.

If an ANY or ALL pattern is qualified with NOT, the negation is pushed down to each clause. NOT LIKE ANY (a, b) is equivalent to NOT LIKE a OR NOT LIKE b. See the examples.

Syntax

string-expression [ NOT ] { LIKE | ILIKE | LIKEB | ILIKEB }
   { pattern | { ANY | SOME | ALL } ( pattern,... ) } [ ESCAPE 'char' ]

Arguments

string-expression

String expression, typically a column, to test for instances of the specified pattern or patterns.

NOT

Returns true if the LIKE predicate returns false and vice-versa. When used with ANY or ALL, applies to each value individually.

LIKE | ILIKE | LIKEB | ILIKEB

Type of comparison:

LIKE: Complies with the SQL standard, case-sensitive, operates on UTF-8 character strings, exact behavior depends on collation parameters such as strength. LIKE is stable for character strings, but immutable for binary strings
ILIKE: Same as LIKE but case-insensitive.
LIKEB: Performs case-sensitive byte-at-a-time ASCII comparisons, immutable for character and binary strings.
ILIKEB: Same as LIKEB but case-insensitive.

pattern

A pattern to test against the expression. Pattern strings can contain the following wildcard characters:

_ (underscore): Match any single character.
% (percent): Match any string of zero or more characters.

ANY | SOME | ALL

Apply a comma-delimited list of patterns, where:

ANY and SOME return true if any pattern matches, equivalent to logical OR. These options are synonyms.
ALL returns true only if all patterns match, equivalent to logical AND.

ESCAPE char

Escape character, by default backslash (\), used to escape reserved characters: wildcard characters (underscore and percent), and the escape character itself.

This option is enforced only for non-default collations; it is currently unsupported with ANY/ALL pattern matching.

Note

Backslash is not valid for binary data type characters. To embed an escape character for binary data types, use ESCAPE to specify a valid binary character.

Substitute symbols

You can substitute the following symbols for LIKE and its variants:

Note

ESCAPE usage is not valid for these symbols.

Symbol	Eqivalent to:
`~~`	LIKE
`~#`	LIKEB
`~~*`	ILIKE
`~#*`	ILIKEB
`!~~`	NOT LIKE
`!~#`	NOT LIKEB
`!~~*`	NOT ILIKE
`!~#*`	NOT ILIKEB

Pattern matching

LIKE and its variants require that the entire string expression match the specified patterns. To match a sequence of characters anywhere within a string, the pattern must start and end with a percent sign.

LIKE does not ignore trailing white space characters. If the data values to match end with an indeterminate amount of white space, append the wildcard character % to pattern.

Locale dependencies

In the default locale, LIKE and ILIKE handle UTF-8 character-at-a-time, locale-insensitive comparisons. ILIKE handles language-independent case-folding.

In non-default locales, LIKE and ILIKE perform locale-sensitive string comparisons, including some automatic normalization, using the same algorithm as the = operator on VARCHAR types.

ESCAPE expressions evaluate to exactly one octet—or one UTF-8 character for non-default locales.

Examples

Basic pattern matching

The following query searches for names with a common prefix:

=> SELECT name FROM people WHERE name LIKE 'Ann%';
   name
-----------
 Ann
 Ann Marie
 Anna
(3 rows)

LIKE ANY/ALL

LIKE operators support the keywords ANY and ALL, which let you specify multiple patterns to test against a string expression. For example, the following query finds all names that begin or end with the letter 'A':

=> SELECT name FROM people WHERE name LIKE ANY ('A%', '%a');
   name
-----------
 Alice
 Ann
 Ann Marie
 Anna
 Roberta
(5 rows)

LIKE ANY usage is equivalent to individual LIKE conditions combined with OR:

=> SELECT name FROM people WHERE name LIKE 'A%' OR name LIKE '%a';
   name
-----------
 Alice
 Ann
 Ann Marie
 Anna
 Roberta
(5 rows)

Similarly, LIKE ALL is equivalent to individual LIKE conditions combined with AND.

NOT LIKE ANY/ALL

You can use NOT with LIKE ANY or LIKE ALL. NOT does not negate the LIKE expression; instead it negates each clause.

Consider a table with the following contents:

=> SELECT name FROM people;
   name
-----------
 Alice
 Ann
 Ann Marie
 Anna
 Richard
 Rob
 Robert
 Roberta
(8 rows)

In the following query, NOT LIKE ANY ('A%', '%a') is equivalent to NOT LIKE 'A%' OR NOT LIKE '%a', so the only result that is eliminated is Anna, which matches both patterns:

=> SELECT name FROM people WHERE name NOT LIKE ANY ('A%', '%a');
   name
-----------
 Alice
 Ann
 Ann Marie
 Richard
 Rob
 Robert
 Roberta
(7 rows)

--- same results:
=> SELECT name FROM people WHERE name NOT LIKE 'A%' OR name NOT LIKE '%a';

NOT LIKE ALL eliminates results that satisfy any pattern:

=> SELECT name FROM people WHERE name NOT LIKE ALL ('A%', '%a');
  name
---------
 Richard
 Rob
 Robert
(3 rows)

--- same results:
=> SELECT name FROM people WHERE name NOT LIKE 'A%' AND name NOT LIKE '%a';

Pattern matching in locales

The following example illustrates pattern matching in locales.

=> \locale default
INFO 2567:  Canonical locale: 'en_US'
Standard collation: 'LEN_KBINARY'
English (United States)
=> CREATE TABLE src(c1 VARCHAR(100));
=> INSERT INTO src VALUES (U&'\00DF'); --The sharp s (ß)
=> INSERT INTO src VALUES ('ss');
=> COMMIT;

Querying the src table in the default locale returns both ss and sharp s.

=> SELECT * FROM src;
 c1
----
 ß
 ss
(2 rows)

The following query combines pattern-matching predicates to return the results from column c1:

=> SELECT c1, c1 = 'ss' AS equality, c1 LIKE 'ss'
   AS LIKE, c1 ILIKE 'ss' AS ILIKE FROM src;
 c1 | equality | LIKE | ILIKE
----+----------+------+-------
 ß  | f        | f    | f
 ss | t        | t    | t
(2 rows)

The next query specifies unicode format for c1:

=> SELECT c1, c1 = U&'\00DF' AS equality,
   c1 LIKE U&'\00DF' AS LIKE,
   c1 ILIKE U&'\00DF' AS ILIKE from src;
 c1 | equality | LIKE | ILIKE
----+----------+------+-------
 ß  | t        | t    | t
 ss | f        | f    | f
(2 rows)

Now change the locale to German with a strength of 1 (ignore case and accents):

=> \locale LDE_S1
INFO 2567:  Canonical locale: 'de'
Standard collation: 'LDE_S1'
German  Deutsch
=> SELECT c1, c1 = 'ss' AS equality,
c1 LIKE 'ss' as LIKE, c1 ILIKE 'ss' AS ILIKE from src;
 c1 | equality | LIKE | ILIKE
----+----------+------+-------
 ß  | t        | t    | t
 ss | t        | t    | t
(2 rows)

This example illustrates binary data types with pattern-matching predicates:

=> CREATE TABLE t (c BINARY(1));
CREATE TABLE
=> INSERT INTO t VALUES (HEX_TO_BINARY('0x00')), (HEX_TO_BINARY('0xFF'));
 OUTPUT
--------
      2
(1 row)

=> COMMIT;
COMMIT
=> SELECT TO_HEX(c) FROM t;
 TO_HEX
--------
 00
 ff
(2 rows)

=> SELECT * FROM t;
  c
------
 \000
 \377
(2 rows)

=> SELECT c, c = '\000', c LIKE '\000', c ILIKE '\000' from t;
  c   | ?column? | ?column? | ?column?
------+----------+----------+----------
 \000 | t        | t        | t
 \377 | f        | f        | f
(2 rows)

=> SELECT c, c = '\377', c LIKE '\377', c ILIKE '\377' FROM t;
  c   | ?column? | ?column? | ?column?
------+----------+----------+----------
 \000 | f        | f        | f
 \377 | t        | t        | t
(2 rows)