Categories:

String & Binary Functions (Matching/Comparison)

LIKE

Allows case-sensitive matching of strings based on comparison with a pattern. For case-insensitive matching, use ILIKE instead.

If the subject string matches the pattern, this returns the subject string.

LIKE, ILIKE, and RLIKE all perform similar operations; however, RLIKE uses POSIX EXE (Extended Regular Expression) syntax instead of the SQL pattern syntax used by LIKE and ILIKE.

See also

ILIKE , RLIKE , LIKE ANY

Syntax

<subject> LIKE <pattern> [ ESCAPE <escape> ]

LIKE( <subject> , <pattern> [ , <escape> ] )

Arguments

Required:

subject

Subject to match. This is typically a VARCHAR, although some other data types can be used.

pattern

Pattern to match. This is typically a VARCHAR, although some other data types can be used.

Optional:

escape

Character(s) inserted in front of a wildcard character to indicate that the wildcard should be interpreted as a regular character and not as a wildcard.

Returns

The data type of the returned value is the same as the data type of the subject.

Usage Notes

  • SQL wildcards are supported in pattern:

    • An underscore (_) matches any single character.

    • A percent sign (%) matches any sequence of zero or more characters.

  • Wildcards in pattern include newline characters (\n) in subject as matches.

  • LIKE pattern matching covers the entire string. To match a sequence anywhere within a string, start and end the pattern with %.

  • NULL does not match NULL. In other words, if the subject is NULL and the pattern is NULL, that is not considered a match.

Collation Details

Arguments with collation specifications are currently not supported.

Examples

The following examples show the use of LIKE, NOT LIKE, and the wildcard character %:

CREATE OR REPLACE TABLE like_ex(subject varchar(20));
INSERT INTO like_ex VALUES
    ('John  Dddoe'),
    ('Joe   Doe'),
    ('John_down'),
    ('Joe down'),
    ('Elaine'),
    (''),    -- empty string
    (null);
SELECT * FROM like_ex WHERE subject LIKE '%Jo%oe%';
+-------------+
| SUBJECT     |
|-------------|
| John  Dddoe |
| Joe   Doe   |
+-------------+
SELECT * FROM like_ex WHERE subject LIKE '%J%h%^_do%' ESCAPE '^';
+-----------+
| SUBJECT   |
|-----------|
| John_down |
+-----------+
SELECT * FROM like_ex WHERE subject NOT LIKE 'John%';
+-----------+
| SUBJECT   |
|-----------|
| Joe   Doe |
| Joe down  |
| Elaine    |
|           |
+-----------+
SELECT * FROM like_ex WHERE subject NOT LIKE '';
+-------------+
| SUBJECT     |
|-------------|
| John  Dddoe |
| Joe   Doe   |
| John_down   |
| Joe down    |
| Elaine      |
+-------------+