Menu
Amazon Redshift
Database Developer Guide (API Version 2012-12-01)

LIKE

The LIKE operator compares a string expression, such as a column name, with a pattern that uses the wildcard characters % (percent) and _ (underscore). LIKE pattern matching always covers the entire string. To match a sequence anywhere within a string, the pattern must start and end with a percent sign.

LIKE is case-sensitive; ILIKE is case-insensitive.

Syntax

Copy
expression [ NOT ] LIKE | ILIKE pattern [ ESCAPE 'escape_char' ]

Arguments

expression

A valid UTF-8 character expression, such as a column name.

LIKE | ILIKE

LIKE performs a case-sensitive pattern match. ILIKE performs a case-insensitive pattern match for single-byte characters. Both LIKE and ILIKE perform a case-insensitive pattern match for multibyte characters.

pattern

A valid UTF-8 character expression with the pattern to be matched.

escape_char

A character expression that will escape metacharacters characters in the pattern. The default is two backslashes ('\\').

If pattern does not contain metacharacters, then the pattern only represents the string itself; in that case LIKE acts the same as the equals operator.

Either of the character expressions can be CHAR or VARCHAR data types. If they differ, Amazon Redshift converts pattern to the data type of expression.

LIKE supports the following pattern-matching metacharacters:

Operator Description
% Matches any sequence of zero or more characters.
_ Matches any single character.

Examples

The following table shows examples of pattern matching using LIKE:

Expression Returns
'abc' LIKE 'abc' True
'abc' LIKE 'a%' True
'abc' LIKE '_B_' False
'abc' ILIKE '_B_' True
'abc' LIKE 'c%' False

The following example finds all cities whose names start with "E":

Copy
select distinct city from users where city like 'E%' order by city; city --------------- East Hartford East Lansing East Rutherford East St. Louis Easthampton Easton Eatontown Eau Claire ...

The following example finds users whose last name contains "ten" :

Copy
select distinct lastname from users where lastname like '%ten%' order by lastname; lastname ------------- Christensen Wooten ...

The following example finds cities whose third and fourth characters are "ea". The command uses ILIKE to demonstrate case insensitivity:

Copy
select distinct city from users where city ilike '__EA%' order by city; city ------------- Brea Clearwater Great Falls Ocean City Olean Wheaton (6 rows)

The following example uses the default escape string (\\) to search for strings that include "_":

Copy
select tablename, "column" from pg_table_def where "column" like '%start\\_%' limit 5; tablename | column -------------------+--------------- stl_s3client | start_time stl_tr_conflict | xact_start_ts stl_undone | undo_start_ts stl_unload_log | start_time stl_vacuum_detail | start_row (5 rows)

The following example specifies '^' as the escape character, then uses the escape character to search for strings that include "_":

Copy
select tablename, "column" from pg_table_def where "column" like '%start^_%' escape '^' limit 5; tablename | column -------------------+--------------- stl_s3client | start_time stl_tr_conflict | xact_start_ts stl_undone | undo_start_ts stl_unload_log | start_time stl_vacuum_detail | start_row (5 rows)