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.

Collation:Not supported
See also:ILIKE , RLIKE , LIKE ANY

Syntax

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

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

Arguments

Required:

subject
Subject to match.
pattern
Pattern to match.

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 VARCHAR.

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.

Examples

CREATE OR REPLACE TABLE like_ex(subject varchar(20));
  INSERT INTO like_ex VALUES
  ('John  Dddoe'),
  ('Joe   Doe'),
  ('John_down'),
  ('Joe down'),
  (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   |
+-------------+