Working with LIKE in Postgres

Intro

The LIKE operator allows to filter values based on if a string contains a specified pattern or not. In this article, we will learn how to use the LIKE operator in PostgreSQL.

The Syntax

The basic syntax of BETWEEN is as follows:

[value] LIKE [pattern]

PostgreSQL provides two wildcard characters for pattern matching.

• We can use the percentage % to match any string of 0 or more characters.
• We can use the underscore _ to match a single character.

Getting Setup

For our setup, we will use docker compose to create a Postgres database and to connect phpmyadmin. Start by copying the following into a docker compose file called docker-compose.yml

version: '3'
 
services:
  db:
    image: 'postgres:latest'
    ports:
      - 5432:5432
    environment:
      POSTGRES_USER: username
      POSTGRES_PASSWORD: password
      POSTGRES_DB: default_database
    volumes:
      - psqldata:/var/lib/postgresql

  phpmyadmin:
    image: phpmyadmin/phpmyadmin
    links:
      - db
    environment:
      PMA_HOST: db
      PMA_PORT: 3306
      PMA_ARBITRARY: 1
    restart: always
    ports:
      - 8081:80

volumes:
  psqldata:

We can run this file, we can use docker-compose up. One this is done, open up phpmyadmin by going to http://localhost:8081.

You can then login by leaving the host empty and using the following credentials.

POSTGRES_USER: username
POSTGRES_PASSWORD: password

Creating a DB

In this article, we will need some data to work with. If you don't understand these commands, don't worry, we will cover them in later articles.

We will be using the sample db provided here: https://dev.Postgres.com/doc/sakila/en/. However, we will only enter what we need rather than import the whole db.

Next, let's create an actor table.

CREATE TABLE actor (
  actor_id smallint,
  first_name VARCHAR(45) NOT NULL,
  last_name VARCHAR(45) NOT NULL,
  last_update TIMESTAMP(0) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY  (actor_id)
) ;

And finally, let's enter a few rows.

INSERT INTO actor VALUES 
(1,'PENELOPE','GUINESS','2006-02-15 04:34:33'),
(2,'NICK','WAHLBERG','2006-02-15 04:34:33'),
(3,'ED','CHASE','2006-02-15 04:34:33'),
(4,'JENNIFER','DAVIS','2006-02-15 04:34:33'),
(5,'JOHNNY','LOLLOBRIGIDA','2006-02-15 04:34:33'),
(6,'BETTE','NICHOLSON','2006-02-15 04:34:33'),
(7,'GRACE','MOSTEL','2006-02-15 04:34:33'),
(8,'MATTHEW','JOHANSSON','2006-02-15 04:34:33')

Like operator using the percentage %

The percentage operator matches 0 or more characters. For example, we if use LIKE 'A%'we can match names such as Andy or Adam.

select * from actor where last_name like 'g%';

We can also use % around a string to match a substring. For example %on% will match Patterson or Thompson.

select * from actor where last_name like '%as%';

We can also use the NOT keyword to find items that don’t match.

select * from actor where last_name not like '%as%';

Example using the underscore character

We can use the _ underscore character to match items missing a single value. For example, a_am will match adam since we are only missing the d.

select * from actor where last_name like 'D_VIS';