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');