Hello. In this tutorial, we will learn about the SQL like statement vs the ilike and we will use the PostgreSQL database running on Docker.
1. Introduction
SQL stands for Structured Query Language and is used to extract and organize data stored in relational databases like MySQL, PostgreSQL, Oracle, etc. A relational database consists of rows and columns that allow fetching specific information from databases that can be used later for analysis. In real-time SQL manages a large amount of data that is written and read simultaneously and any query that reaches the SQL server is processed into three parts –
- Parsing – Process to check the SQL query syntax
- Binding – Process to check the SQL query semantics
- Optimization – Process to generate the SQL query execution plan
1.1 Usage of SQL
Structured Query Language (popularly known as SQL) is commonly used by data analysts and data science professionals and is helpful too –
- Execute queries against the database
- Retrieve data from the database
- Insert new records into the database
- Update existing records into the database
- Created stored procedures, functions, and materialized views in the database
- Create users and grant permissions
- Set permissions on tables, stored procedures, functions, and materialized views
1.2 What is a LIKE operator?
The LIKE operator in SQL query is used with the where keyword and helps to fetch records based on the pattern matching. It is represented by the below syntax –
LIKE condition in sql
1.3 What is the ILIKE operator?
The ILIKE operator in sql query is similar to LIKE but it matches values case-insensitively. It is represented by the below syntax –
ILIKE condition in sql
expression ILIKE pattern
1.4 Pattern expression
The pattern expression in LIKE and ILIKE operators contain the pattern matching. There are two types of patterns available –
%: Allows matching any string of any length including zero length_: Allows matching on a single character
2. SQL Like Statement vs iLike
Let us dive into some practice implementation on the PostgreSQL database.
2.1 Pre-requirement – Postgres Setup
Usually, setting up the database is a tedious step but with Docker, it is a simple process. You can watch the video available at this link to understand the Docker installation on Windows OS. Once done open the terminal and trigger the below command to set and run PostgreSQL.
Docker commands
-- command to run postgres on docker -- -- remember to change the password -- docker run -d -p 5433:5432 -e POSTGRES_PASSWORD= --name postgres postgres -- command to stop the Postgres docker container -- docker stop postgres -- command to remove the Postgres docker container -- docker rm postgres
Remember to enter the password of your choice. If everything goes well the PostgreSQL database server would be up and running on a port number – 5433 and you can connect with the Dbeaver GUI tool for connecting to the server.
2.2. Creating a database
To practice this tutorial I will be using the learning database. You can below command to create a database.
Create database sql query
-- create database create database learning
2.3 Creating a Person table and inserting data
Create a person table in the learning database. You can use the below script to create a table, insert some data into it and view it.
Person sql script
-- drop table
drop table person;
-- create person table
create table person (
id SERIAL PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
gender VARCHAR(50),
email VARCHAR(50),
date_of_birth DATE,
country_of_birth VARCHAR(50)
);
-- insert sample records in person table
insert into person (first_name, last_name, gender, email, date_of_birth, country_of_birth) values ('Had', 'Aronsohn', 'Male', 'haronsohn0@macromedia.com', '2016-01-03', 'Portugal');
insert into person (first_name, last_name, gender, email, date_of_birth, country_of_birth) values ('Katya', 'Trainor', 'Female', 'ktrainor1@typepad.com', '1984-07-05', 'Canada');
insert into person (first_name, last_name, gender, email, date_of_birth, country_of_birth) values ('Angelle', 'Conahy', 'Female', 'aconahy2@yandex.ru', '1992-02-24', 'Afghanistan');
insert into person (first_name, last_name, gender, email, date_of_birth, country_of_birth) values ('Enrika', 'Osmond', 'Female', 'eosmond3@sun.com', '1984-03-11', 'Indonesia');
insert into person (first_name, last_name, gender, email, date_of_birth, country_of_birth) values ('Xever', 'Foldes', 'Male', 'xfoldes4@eepurl.com', '1977-06-22', 'Philippines');
insert into person (first_name, last_name, gender, email, date_of_birth, country_of_birth) values ('Egbert', 'Badam', 'Male', 'ebadam5@list-manage.com', '1987-10-02', 'Colombia');
insert into person (first_name, last_name, gender, email, date_of_birth, country_of_birth) values ('Jaquenetta', 'Goodison', 'Female', 'jgoodison6@tripod.com', '1985-03-01', 'Costa Rica');
insert into person (first_name, last_name, gender, email, date_of_birth, country_of_birth) values ('Ardene', 'Hunnywell', 'Female', 'ahunnywell7@howstuffworks.com', '2011-09-16', 'Philippines');
insert into person (first_name, last_name, gender, email, date_of_birth, country_of_birth) values ('Ruperta', 'Druhan', 'Female', 'rdruhan8@taobao.com', '1971-11-27', 'Faroe Islands');
insert into person (first_name, last_name, gender, email, date_of_birth, country_of_birth) values ('Niels', 'Lorenz', 'Male', 'nlorenz9@theguardian.com', '2012-07-10', 'Sweden');
insert into person (first_name, last_name, gender, email, date_of_birth, country_of_birth) values ('Hamid', 'Mattek', 'Male', 'hmatteka@dedecms.com', '2010-10-06', 'Indonesia');
insert into person (first_name, last_name, gender, email, date_of_birth, country_of_birth) values ('Andras', 'Antognoni', 'Male', 'aantognonib@jalbum.net', '2011-05-16', 'Iran');
insert into person (first_name, last_name, gender, email, date_of_birth, country_of_birth) values ('Hermia', 'Dymocke', 'Female', 'hdymockec@dion.ne.jp', '1977-11-11', 'Brazil');
insert into person (first_name, last_name, gender, email, date_of_birth, country_of_birth) values ('Bendix', 'Towlson', 'Male', 'btowlsond@nytimes.com', '2020-05-09', 'Armenia');
insert into person (first_name, last_name, gender, email, date_of_birth, country_of_birth) values ('Minny', 'O''Corrin', 'Female', 'mocorrine@woothemes.com', '2021-09-07', 'China');
insert into person (first_name, last_name, gender, email, date_of_birth, country_of_birth) values ('Brooks', 'MacDwyer', 'Male', 'bmacdwyerf@blogger.com', '2000-10-25', 'China');
insert into person (first_name, last_name, gender, email, date_of_birth, country_of_birth) values ('Catha', 'Lavigne', 'Female', 'clavigneg@discovery.com', '1990-05-01', 'Portugal');
insert into person (first_name, last_name, gender, email, date_of_birth, country_of_birth) values ('Shay', 'Charle', 'Male', 'scharleh@sfgate.com', '2001-09-11', 'Indonesia');
insert into person (first_name, last_name, gender, email, date_of_birth, country_of_birth) values ('Lenee', 'Haskur', 'Female', 'lhaskuri@redcross.org', '1974-12-14', 'East Timor');
insert into person (first_name, last_name, gender, email, date_of_birth, country_of_birth) values ('Fayette', 'Luker', 'Female', 'flukerj@squidoo.com', '1998-03-25', 'Sweden');
insert into person (first_name, last_name, gender, email, date_of_birth, country_of_birth) values ('Don', 'Applewhite', 'Male', 'dapplewhitek@latimes.com', '1976-08-09', 'Iran');
insert into person (first_name, last_name, gender, email, date_of_birth, country_of_birth) values ('Ric', 'Peggrem', 'Male', 'rpeggreml@domainmarket.com', '1993-01-28', 'Brazil');
insert into person (first_name, last_name, gender, email, date_of_birth, country_of_birth) values ('Aprilette', 'Mattiazzi', 'Female', 'amattiazzim@nifty.com', '1994-10-10', 'Germany');
insert into person (first_name, last_name, gender, email, date_of_birth, country_of_birth) values ('Lindy', 'Garvill', 'Male', 'lgarvilln@un.org', '1972-12-05', 'Brazil');
insert into person (first_name, last_name, gender, email, date_of_birth, country_of_birth) values ('Francisco', 'Lebbern', 'Male', 'flebberno@accuweather.com', '1987-12-11', 'China');
insert into person (first_name, last_name, gender, email, date_of_birth, country_of_birth) values ('Elwood', 'Cumberbatch', 'Male', 'ecumberbatchp@businessweek.com', '2008-12-28', 'Czech Republic');
… other insert statements are omitted for brevity
-- fetch all records from the person table
select * from person;
Once the data is viewed in the person table it will be shown below.
2.4 Playing with the like statement with percentage and underscore keyword
Let us see some examples.
2.4.1 Fetch records that contain ‘accuweather.com’ in the email address
The below sql query will fetch the records from the person table that contains the ‘accuweather.com’ in the email address.
Sql query snippet
-- fetch those records which contain 'accuweather.com' in the email address from the person table select id, first_name, last_name, gender, email, date_of_birth, country_of_birth from person where email like '%accuweather.com';
If everything goes well the below result will be shown.
2.4.2 Fetch records that contain first_name with the letter ‘er’ in any position
The below SQL query will fetch the records from the person table that contains first_name with the letter ‘er’ in any position.
Sql query snippet
-- fetch those records which contain first_name with the letter 'er' in any position from the person table select id, first_name, last_name, gender, email, date_of_birth, country_of_birth from person where first_name like '%er%';
If everything goes well the below result will be shown.
2.4.3 Fetch records that contain country_of_birth starting with the letter ‘I’
The below SQL query will fetch those records from the person table that contains country_of_birth starting with the letter ‘I’.
Sql query snippet
-- fetch those records which contain country_of_birth starting with the letter 'I' from the person table select id, first_name, last_name, gender, email, date_of_birth, country_of_birth from person where country_of_birth like 'I%';
If everything goes well the below result will be shown.
2.4.4 Fetch records whose first_name begins with any character followed by the letter ‘o’
The below SQL query will fetch those records from the person table whose first_name begins with any character followed by the letter ‘o’.
Sql query snippet
-- fetch those records whose first_name begins with any character followed by the letter 'o' from the person table select id, first_name, last_name, gender, email, date_of_birth, country_of_birth from person where last_name like '_o%';
If everything goes well the below result will be shown.
2.4.5 Fetch records that contain first_name only with 8 characters
The below SQL query will fetch those records from the person table which contains first_name only with 8 characters.
Sql query snippet
-- fetch those records which contain first_name only with 8 characters from the person table select id, first_name, last_name, gender, email, date_of_birth, country_of_birth from person where first_name like '________';
If everything goes well the below result will be shown.
2.5 Playing with the ILIKE operator
Let us see an example.
2.5.1 Fetch records that contain first_name starting with the letter ‘KA’
The below SQL query will fetch the records from the person table that contains first_name starting with the letter ‘KA’.
Sql query snippet
-- fetch those records which contain first_name starting with the letter 'KA' from the person table select id, first_name, last_name, gender, email, date_of_birth, country_of_birth from person where first_name ilike 'KA%';
If everything goes well the below result will be shown.
3. Summary
In this tutorial, we learned the brief of SQL LIKE & ILIKE operators, and their practical implementation via different examples. You can download the SQL scripts and docker commands used in this tutorial from the Downloads section.
4. Download the SQL scripts
This was a tutorial to understand the SQL like statement and ilike.
You can download the full source code of this example here: SQL Like Statement vs iLike
