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.

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

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.

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

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   |
+-------------+