String & Binary Functions (Matching/Comparison)
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.
<subject> LIKE <pattern> [ ESCAPE <escape> ] LIKE( <subject> , <pattern> [ , <escape> ] )
Subject to match. This is typically a VARCHAR, although some other data types can be used.
Pattern to match. This is typically a VARCHAR, although some other data types can be used.
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.
The data type of the returned value is the same as the data type of the
SQL wildcards are supported in
An underscore (
_) matches any single character.
A percent sign (
%) matches any sequence of zero or more characters.
patterninclude newline characters (
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.
Arguments with collation specifications are currently not supported.
The following examples show the use of
NOT LIKE, and the wildcard
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 | +-------------+