LIKE

The LIKE is used for pattern matching to find similar strings in data. It’s often employed in the WHERE clause to filter results based on specific patterns. LIKE is case-sensitive; use ILIKE for case-insensitive pattern matching.

Syntax

<expression> LIKE '<pattern>'

Parameters

Parameter	Description	Supported input types
`<expression>`	Any expression that evaluates to `TEXT`.	`TEXT`
`<pattern>`	Specifies the pattern to match (case-sensitive). The pattern can be a constant or any `TEXT` expression, including a column reference.	`TEXT`. SQL wildcards are supported: * Use an underscore (`_`) to match any single character * Use a percent sign (`%`) to match any number of any characters, including no characters.

Return Type

BOOLEAN

Example

The following example finds all players whose nickname starts with joe using the % wildcard, which matches any sequence of characters:

SELECT playerid, nickname, email
FROM players
WHERE nickname LIKE 'joe%'
ORDER BY playerid;

playerid int null	nickname text null	email text null
145	joel53	james96@example.com
1356	joel35	smithaustin@example.net
2823	joe12	thompsondana@example.org
3232	joe30	gabrielle32@example.org
3872	joemata	nashrodney@example.com
4655	joel68	browntina@example.org
4880	joel47	kristinarivas@example.com

Rows: 7Execution time: 2.70ms

LIKE is case-sensitive. The following example returns no results because no nicknames start with uppercase 'Joe':

SELECT playerid, nickname, email
FROM players
WHERE nickname LIKE 'Joe%';

playerid int null	nickname text null	email text null

Rows: 0Execution time: 6.48ms

The _ wildcard matches exactly one character. The following example finds players whose nickname contains oe, followed by any single character, then e:

SELECT playerid, nickname, email
FROM players
WHERE nickname LIKE '%oe_e%'
ORDER BY playerid;

playerid int null	nickname text null	email text null
2845	jonathanschroeder	donnaglenn@example.net
3079	ianschroeder	cfarmer@example.com
4329	jennyschroeder	phillipsdana@example.org
4787	schroederkim	htapia@example.net

Rows: 4Execution time: 7.61ms

Example using a non-constant pattern

The pattern doesn’t have to be a literal. You can match each row against a pattern stored in another column. For example, given a filters table that holds one pattern per row, join it with players to return the nicknames that match any of the patterns:

SELECT
    p.nickname, f.pattern
FROM
    players p
JOIN
    filters f ON p.nickname LIKE f.pattern;

If the pattern column contains joe% and %long, this returns nicknames such as joe12, joemata, and joshualong.

Overview

Performance and Observability

Security

Self-Managed

Managed service

Guides

SQL reference

Release notes

API reference

Legal

Syntax

Parameters

Return Type

Example

Example using a non-constant pattern

​Syntax

​Parameters

​Return Type

​Example

​Example using a non-constant pattern

Syntax

Parameters

Return Type

Example

Example using a non-constant pattern