Languages
[Edit]
EN

PostgreSQL - LIKE operator

0 points
Created by:
Nabila-Burnett
265

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

Cross technology - LIKE operator

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