LIKE

Reference material for LIKE function

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	[e-mail]
1356	joel35	[e-mail]
2823	joe12	[e-mail]
3232	joe30	[e-mail]
3872	joemata	[e-mail]
4655	joel68	[e-mail]
4880	joel47	[e-mail]

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	[e-mail]
3079	ianschroeder	[e-mail]
4329	jennyschroeder	[e-mail]
4787	schroederkim	[e-mail]

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.