EN
PostgreSQL - LIKE operator
0
points
In this article, we would like to show you how to use LIKE
operator in PostgreSQL.
Quick solution:
SELECT "column1", "column2", ...
FROM "table_name"
WHERE "column_name" LIKE pattern;
We use LIKE
operator in a WHERE
clause to search for a specified pattern in a column.
There are two wildcards used with the LIKE
operator:
%
- matches 0 or any number of characters (0, 1, 2, 3, ...)_
- matches only one single CHARACTER (1)
LIKE operator | Description |
---|---|
WHERE "username" LIKE 'prefix%' | Finds any values that start with "prefix". |
WHERE "username" LIKE '%postfix' | Finds any values that end with "postfix". |
WHERE "username" LIKE '%middle%' | Finds any values that have "or" in any position. |
WHERE u.username LIKE 'part1%part2%part3' | Finds any values that starts with "part1", have "part2" in any position and end with "part3" |
Practical example
Let's say we have users
database that contains usernames.
User 'John' can be matched by any of the following lines:
J%
Jo%
%n
%hn
%h%
%oh%
J___
Jo__
___n
__hn
_oh_
To show how the LIKE
operator works, we will use the following table:
Note:
At the end of this article you can find database preparation SQL queries.
Example
In this example, we will display information about user Jonh using LIKE
operator using few combinations of wildcards. All of the queries below give the same result.
Queries:
SELECT * FROM "users"
WHERE "name" LIKE 'J%';
SELECT * FROM "users"
WHERE "name" LIKE 'Jo%';
SELECT * FROM "users"
WHERE "name" LIKE '%hn';
SELECT * FROM "users"
WHERE "name" LIKE '_oh_';
SELECT * FROM "users"
WHERE "name" LIKE 'J_h%';
Result:
Database preparation
create_tables.sql
file:
CREATE TABLE "users" (
"id" SERIAL,
"name" VARCHAR(50) NOT NULL,
"surname" VARCHAR(50) NOT NULL,
"department_id" INTEGER,
"salary" DECIMAL(15,2) NOT NULL,
PRIMARY KEY ("id")
);
insert_data.sql
file:
INSERT INTO "users"
( "name", "surname", "department_id", "salary")
VALUES
('John', 'Stewart', 1, '3512.00'),
('Chris', 'Brown', 2, '1344.00'),
('Kate', 'Lewis', 3, '6574.00'),
('Ailisa', 'Gomez', NULL, '6500.00'),
('Gwendolyn', 'James', 2, '4200.00'),
('Simon', 'Collins', 4, '3320.00'),
('Taylor', 'Martin', 2, '1500.00'),
('Andrew', 'Thompson', NULL, '2100.00');