Languages
[Edit]
EN

PostgreSQL - LIKE operator

0 points
Created by:
Nabila-Burnett
385

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 operatorDescription
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:

PostgreSQL - example data used with AVG() function
PostgreSQL - example data used with LIKE operator

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:

PostgreSQL - LIKE operator result
PostgreSQL - LIKE operator 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');
Donate to Dirask
Our content is created by volunteers - like Wikipedia. If you think, the things we do are good, donate us. Thanks!
Join to our subscribers to be up to date with content, news and offers.

PostgreSQL

Native Advertising
🚀
Get your tech brand or product in front of software developers.
For more information Contact us
Dirask - we help you to
solve coding problems.
Ask question.

❤️💻 🙂

Join