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.

Tip

You can use the search optimization service to improve the performance of queries that call this function. For details, see Search Optimization Service.

See also

ILIKE , RLIKE , LIKE ANY

Syntax¶

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

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

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.

  • There is no default escape character.

  • If you use the backslash as an escape character, then you must specify escape the backslash in the ESCAPE clause. For example, the following command specifies that the escape character is the backslash, and then uses that escape character to search for ‘%’ as a literal (without the escape character, the ‘%’ would be treated as a wildcard):

    'SOMETHING%' LIKE '%\\%%' ESCAPE '\\';
    Copy

    For examples of using escape characters, and in particular the backslash as an escape character, see Examples.

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);
Copy
SELECT subject
    FROM like_ex
    WHERE subject LIKE '%Jo%oe%'
    ORDER BY subject;
+-------------+
| SUBJECT     |
|-------------|
| Joe   Doe   |
| John  Dddoe |
+-------------+
Copy
SELECT subject
    FROM like_ex
    WHERE subject LIKE '%J%h%^_do%' ESCAPE '^'
    ORDER BY subject;
+-----------+
| SUBJECT   |
|-----------|
| John_down |
+-----------+
Copy
SELECT subject
    FROM like_ex
    WHERE subject NOT LIKE 'John%'
    ORDER BY subject;
+-----------+
| SUBJECT   |
|-----------|
|           |
| Elaine    |
| Joe   Doe |
| Joe down  |
+-----------+
Copy
SELECT subject
    FROM like_ex
    WHERE subject NOT LIKE ''
    ORDER BY subject;
+-------------+
| SUBJECT     |
|-------------|
| Elaine      |
| Joe   Doe   |
| Joe down    |
| John  Dddoe |
| John_down   |
+-------------+
Copy

The following example uses an ESCAPE clause:

INSERT INTO like_ex (subject) VALUES 
    ('100 times'),
    ('1000 times'),
    ('100%');
Copy

Without the escape character, the percent sign (‘%’) is treated as a wildcard:

SELECT * FROM like_ex WHERE subject LIKE '100%'
    ORDER BY 1;
+------------+
| SUBJECT    |
|------------|
| 100 times  |
| 100%       |
| 1000 times |
+------------+
Copy

With the escape character, the percent sign (‘%’) is treated as a literal:

SELECT * FROM like_ex WHERE subject LIKE '100^%' ESCAPE '^'
    ORDER BY 1;
+---------+
| SUBJECT |
|---------|
| 100%    |
+---------+
Copy

The following example uses an ESCAPE clause in which the backslash is the escape character. Note that the backslash itself must be escaped in both the ESCAPE clause and in the expression:

SELECT * FROM like_ex WHERE subject LIKE '100\\%' ESCAPE '\\'
    ORDER BY 1;
+---------+
| SUBJECT |
|---------|
| 100%    |
+---------+
Copy
Language: English