LIKE¶

The LIKE function tests if a string matches a given pattern. LIKE is similar to RLIKE are similar in that LIKE uses SQL patterns, whereas RLIKE uses POSIX regular expressions.

For related functions, see the following:

Syntax¶

string_expr [ NOT ] LIKE string_test_expr

Arguments¶

Parameter	Description
`string_expr`	String to test
`string_test_expr`	Test pattern

Test patterns¶

Pattern	Description
`%`	match zero or more characters
`_` (underscore)	match exactly one character
`[A-Z]`	match any character between `A` and `Z` inclusive. The `-` character between two other characters forms a range that matches all characters from the first character to the second. For example, [A-Z] matches all ASCII capital letters.
`[^A-Z]`	match any character not between `A` and `Z`
`[abcde]`	match any one of `a` `b` `c` `d` and `e`
`[^abcde]`	match any character that is not one of `a` `b` `c` `d` or `e`
`[abcC-F]`	match `a` `b` `c` or any character between `C` and `F`

\ (backslash) - escape character

Using a backslash (\) indicates that the wildcard is interpreted as a regular character and not as a wildcard.

Returns¶

TRUE if the test string matches the pattern, or FALSE otherwise.

Notes¶

The test pattern must be literal string. If matching just the beginning of the string, use ISPREFIXOF which supports column references.
Starting with version 2020.3.1, Column references or complex expressions are also supported.
If the value is NULL, the result is NULL.

Examples¶

For these examples, assume a table named nba, with the following structure:

CREATE TABLE nba
(
   Name varchar(40),
   Team varchar(40),
   Number tinyint,
   Position varchar(2),
   Age tinyint,
   Height varchar(4),
   Weight real,
   College varchar(40),
   Salary float
 );

Here’s a peek at the table contents (Download nba.csv):

nba.csv¶
Name	Team	Number	Position	Age	Height	Weight	College	Salary
Avery Bradley	Boston Celtics	0.0	PG	25.0	6-2	180.0	Texas	7730337.0
Jae Crowder	Boston Celtics	99.0	SF	25.0	6-6	235.0	Marquette	6796117.0
John Holland	Boston Celtics	30.0	SG	27.0	6-5	205.0	Boston University
R.J. Hunter	Boston Celtics	28.0	SG	22.0	6-5	185.0	Georgia State	1148640.0
Jonas Jerebko	Boston Celtics	8.0	PF	29.0	6-10	231.0		5000000.0
Amir Johnson	Boston Celtics	90.0	PF	29.0	6-9	240.0		12000000.0
Jordan Mickey	Boston Celtics	55.0	PF	21.0	6-8	235.0	LSU	1170960.0
Kelly Olynyk	Boston Celtics	41.0	C	25.0	7-0	238.0	Gonzaga	2165160.0
Terry Rozier	Boston Celtics	12.0	PG	22.0	6-2	190.0	Louisville	1824360.0

Match the beginning of a string¶

nba=> SELECT "Name","Age","Salary","Team" FROM nba WHERE "Team" LIKE 'Portland%' LIMIT 5;
Name            | Age | Salary  | Team
----------------+-----+---------+-----------------------
Cliff Alexander |  20 |  525093 | Portland Trail Blazers
Al-Farouq Aminu |  25 | 8042895 | Portland Trail Blazers
Pat Connaughton |  23 |  625093 | Portland Trail Blazers
Allen Crabbe    |  24 |  947276 | Portland Trail Blazers
Ed Davis        |  27 | 6980802 | Portland Trail Blazers

Tip

ISPREFIXOF is a more performant way to match the beginning of a string, especially This example can be written as

SELECT "Name","Age","Salary","Team" FROM nba WHERE ISPREFIXOF('Portland',"Team") LIMIT 5;

Match a wildcard character by escaping¶

To match a wildcard, escape it with a backslash escape character:

nba=> SELECT "Name" FROM nba WHERE "Name" LIKE '%\_%';
Name            | Age | Salary  | Team
----------------+-----+---------+-----------------------
R.J._Hunter     |  22 | 1148640 | Boston Celtics

Negate with `NOT`¶

nba=> SELECT "Name","Age","Salary","Team" FROM nba WHERE "Team" NOT LIKE 'Portland%' LIMIT 5;
Name          | Age | Salary  | Team
--------------+-----+---------+---------------
Avery Bradley |  25 | 7730337 | Boston Celtics
Jae Crowder   |  25 | 6796117 | Boston Celtics
John Holland  |  27 |         | Boston Celtics
R.J. Hunter   |  22 | 1148640 | Boston Celtics
Jonas Jerebko |  29 | 5000000 | Boston Celtics

Match the middle of a string¶

nba=> SELECT "Name","Age","Salary","Team" FROM nba WHERE "Team" LIKE '%zz%' LIMIT 5;
Name           | Age | Salary  | Team
---------------+-----+---------+------------------
Jordan Adams   |  21 | 1404600 | Memphis Grizzlies
Tony Allen     |  34 | 5158539 | Memphis Grizzlies
Chris Andersen |  37 | 5000000 | Memphis Grizzlies
Matt Barnes    |  36 | 3542500 | Memphis Grizzlies
Vince Carter   |  39 | 4088019 | Memphis Grizzlies

Find players with a middle name or suffix¶

nba=> SELECT "Name","Age","Salary","Team" FROM nba WHERE "Name" LIKE '% % %';
Name                     | Age | Salary  | Team
-------------------------+-----+---------+----------------------
James Michael McAdoo     |  23 |  845059 | Golden State Warriors
Luc Richard Mbah a Moute |  29 |  947276 | Los Angeles Clippers
Larry Nance Jr.          |  23 | 1155600 | Los Angeles Lakers
Metta World Peace        |  36 |  947276 | Los Angeles Lakers
Glenn Robinson III       |  22 | 1100000 | Indiana Pacers
Johnny O'Bryant III      |  23 |  845059 | Milwaukee Bucks
Tim Hardaway Jr.         |  24 | 1304520 | Atlanta Hawks
Frank Kaminsky III       |  23 | 2612520 | Charlotte Hornets
Kelly Oubre Jr.          |  20 | 1920240 | Washington Wizards
Otto Porter Jr.          |  23 | 4662960 | Washington Wizards

Find NON-LITERAL patterns¶

nba=> CREATE TABLE t(x int not null, y text not null, z text not null);
nba=> INSERT INTO t VALUES (1,'abc','a'),(2,'abcd','bc');

Select rows in which z is a prefix of y:¶

nba=> SELECT * FROM t WHERE y LIKE z || '%';

x |  y  | z
-------------
1 | abc | a

Select rows in which y contains z as a substring:¶

nba=> SELECT * FROM t WHERE y LIKE z || '%';

x |  y   | z
--------------
1 | abc  | a
2 | abcd | bc

Values that contain wildcards as well:¶

nba=> CREATE TABLE patterns(x text not null);
nba=> INSERT INTO patterns values ('%'),('a%'),('%a');
nba=> SELECT x, 'abc' LIKE x FROM patterns;

x  |  ?column?
--------------
%  | 1
a% | 1
%a | 0