PostgreSQL LIKE, Not Like, Wildcards (%, _ ) Examples
Juniper Willow
⚡ Smart Summary
PostgreSQL LIKE matches text against patterns using two wildcards — the percent sign and the underscore. This tutorial shows how to use LIKE and NOT LIKE in SELECT statements, with worked psql and pgAdmin examples for each case.
The PostgreSQL LIKE operator matches text values against patterns using wildcards. If the search expression matches the pattern, LIKE returns true. Wildcards work in the WHERE clause of SELECT, UPDATE, INSERT, or DELETE.
What are PostgreSQL Wildcards?
PostgreSQL supports two LIKE wildcards:
- Percent sign (
%): matches zero, one, or many characters or digits. - Underscore (
_): matches exactly one character or digit.
The two symbols can be combined inside the same pattern. If LIKE is used without either wildcard, it behaves like the equals (=) operator.
PostgreSQL LIKE Syntax
The basic LIKE syntax is:
expression LIKE pattern [ ESCAPE 'escape-character' ]- expression — a character expression, typically a column or field name.
- pattern — a character expression that contains wildcards.
- escape-character — optional. Lets you match literal
%or_characters. When omitted, the backslash (\) is the default escape character.
PostgreSQL LIKE with the % Wildcard
The % sign matches zero, one, or more characters. Consider the following Book table:
To find books whose name starts with “Lear”, run the query below.
SELECT *
FROM
Book
WHERE
name LIKE 'Lear%';The query returns:
To find books whose name contains “by” anywhere:
SELECT *
FROM
Book
WHERE
name LIKE '%by%';The query returns:
PostgreSQL LIKE with the _ Wildcard
The _ sign matches exactly one character. The following query finds names where the first character is any single letter, followed by “earn” and then any suffix:
SELECT *
FROM
Book
WHERE
name LIKE '_earn%';The query returns:
Another example — match any text that ends with “Beginner” plus one more character:
SELECT *
FROM
Book
WHERE
name LIKE '%Beginner_';The query returns:
PostgreSQL NOT LIKE Operator
Combine LIKE with NOT to return rows that do not match the pattern. For example, list every book whose name does not start with “Post”:
SELECT *
FROM
Book
WHERE
name NOT LIKE 'Post%';The query returns:
Only one book satisfies the condition. Now list every book whose name does not contain the word “Made”:
SELECT *
FROM
Book
WHERE
name NOT LIKE '%Made%';The query returns:
Three rows satisfy the condition.
Using LIKE with pgAdmin
The same queries can also be run graphically inside pgAdmin’s Query Tool.
% Wildcard in pgAdmin
Step 1) Log in to pgAdmin.
Step 2) In the navigation bar on the left, click Databases, then click Demo.
Step 3) Type the query in the Query Editor:
SELECT *
FROM
Book
WHERE
name LIKE 'Lear%';Step 4) Click Execute.
The result pane shows the matching books:
To search for a book with “by” anywhere in its name:
Step 1) Type the following in the Query Editor:
SELECT *
FROM
Book
WHERE
name LIKE '%by%';Step 2) Click Execute.
The result pane shows:
_ Wildcard in pgAdmin
Step 1) Log in to pgAdmin.
Step 2) In the navigation bar on the left, click Databases, then click Demo.
Step 3) Type the query in the Query Editor:
SELECT *
FROM
Book
WHERE
name LIKE '_earn%';Step 4) Click Execute.
The result pane shows:
Step 5) Run the second example:
- Type the query in the Query Editor:
SELECT *
FROM
Book
WHERE
name LIKE '%Beginner_';- Click Execute.
The result pane shows:
NOT LIKE in pgAdmin
Step 1) Log in to pgAdmin.
Step 2) In the navigation bar on the left, click Databases, then click Demo.
Step 3) To list every book whose name does not start with “Post”, type:
SELECT *
FROM
Book
WHERE
name NOT LIKE 'Post%';Step 4) Click Execute.
The result pane shows:
To list books whose name does not contain the word “Made”:
Step 1) Type the following in the Query Editor:
SELECT *
FROM
Book
WHERE
name NOT LIKE '%Made%';Step 2) Click Execute.
The result pane shows:
Download the database used in this tutorial.
