Hello. In this tutorial, we will learn the SQL wildcard operators and see a practical implementation of them.
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 SQL Wildcard operators
Wildcards operators in sql are used to fetch rows from a sql table based on a matching pattern. The following operators are used in postgresql –
- Using percentage (
%) wildcard – Matches any sequence of zero or more characters - Using underscore (
_) wildcard – Matches any single character
1.2.1 Importance of wildcard operators
2. SQL Wildcard operators
Let us dive into some practice implementation.
2.1 Postgres Setup
Usually, setting up the database is a tedious step but with technological advancements, this process has become simple with the help of Docker. Readers can watch the video available at this link to understand the Docker installation on Windows OS. Open the terminal and trigger the following commands to get the postgresql up and running on the local machine.
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 pgAdmin gui tool for connecting to the server.
2.2 Creating a Sample table
To implement this tutorial I am using the sample SQL table provided below. You’re free to create the below SQL table under the database of your choice. I selected practice database while working.
Create and insert records
-- drop table if exists -- DROP TABLE IF EXISTS person; -- create table -- CREATE TABLE person ( id SERIAL PRIMARY KEY, name varchar(255) default NULL, email varchar(255) default NULL, phone varchar(100) default NULL, company varchar(255), salary varchar(100) default NULL, department TEXT default NULL );
The person table will be created as shown below. Once the table is created we will add some dummy records to the table required for understanding the wildcard operators in sql.
2.3 Adding records
Use the below SQL script to add some mock records to the person table. We will add some twenty records to the table.
Inserting records
-- inserting records to table --
INSERT INTO person (name,email,phone,company,salary,department)
VALUES
('Melanie Peck','hymenaeos@google.ca','(761) 595-1700','Consequat Enim Diam Industries','6625.29','feugiat'),
('Sophia Burns','netus.et@yahoo.ca','(321) 982-3186','Proin Institute','7942.13','Maecenas'),
('Brianna Compton','mollis.integer@aol.org','(848) 733-2858','Dictum Mi Ac PC','8706.08','Mauris'),
('Geraldine Joseph','auctor@outlook.ca','(787) 773-2210','Magna Cras Foundation','5688.08','Vivamus'),
('Hedy Howell','aliquet.sem.ut@icloud.ca','(821) 615-5614','Nec Enim Nunc LLP','9554.55','luctus'),
('Sylvester Giles','id.erat@aol.org','1-234-350-6153','At Velit Pellentesque LLC','1528.87','ornare'),
('Joseph Reed','dolor.egestas.rhoncus@aol.edu','1-922-366-9656','Proin Ultrices Duis PC','7894.03','mollis'),
('Adele Espinoza','ac.turpis@aol.couk','1-876-180-9877','Nisi Mauris Nulla Industries','3892.56','risus'),
('Harding Mcfarland','nulla.dignissim@hotmail.org','1-512-768-9585','Non Corp.','6278.80','Quisque'),
('Pamela Wilson','sem.vitae.aliquam@icloud.ca','(444) 723-6239','Tellus Aenean Egestas Inc.','8189.56','arcu'),
('Tarik Rodriquez','arcu.eu@protonmail.com','1-817-471-1904','Pede Ac Foundation','5219.16','vestibulum'),
('Silas Davis','dictum@outlook.org','1-571-105-5712','Elit Aliquam Institute','9884.85','dolor'),
('Akeem Byrd','eget@outlook.edu','1-724-631-7384','Purus Associates','4909.06','pede'),
('Acton Clayton','et@yahoo.couk','(647) 511-6796','Nulla Associates','8600.19','eu'),
('Rowan Cabrera','phasellus.nulla.integer@yahoo.org','1-148-645-2216','Duis Limited','5297.70','magna'),
('Reuben Cleveland','orci.quis@icloud.com','1-574-270-1682','Dis Parturient Industries','6297.43','fringilla'),
('Joy Potts','ridiculus.mus.donec@hotmail.couk','1-584-952-7868','Ante Blandit Viverra Foundation','3198.84','Suspendisse'),
('Kasimir Rowland','donec@protonmail.net','1-455-497-6287','Nibh Aliquam Ornare LLC','7563.16','Class'),
('Charissa Patrick','eu@icloud.net','1-318-535-2062','Eget PC','9241.81','eu'),
('Carol Daniel','interdum.libero.dui@protonmail.org','1-216-973-3347','Nam Interdum Institute','1028.08','enim');
-- viewing the inserted data --
SELECT id,name,email,phone,company,salary,department FROM person;
Once the sql script is executed successfully the following result will be shown on the pgAdmin console.
2.4 Using percentage wildcard operator
The percentage (%) wildcard operator in sql is used to match any sequence of zero or more characters. We will deep dive into the implementation of this operator with the help of different queries.
2.4.1 Query 1
The first sql query will display the names that end with the letter ‘a’.
Query 1 snippet
/* %a - name end with a */ SELECT id,name,email,phone,company,salary,department FROM person WHERE name LIKE '%a';
Once the sql script is executed successfully the names ending with the letter ‘a’ will be shown on the pgAdmin console.
2.4.2 Query 2
The second sql query will display the names that start with the letter ‘a’.
Query 2 snippet
/* a% -> name starts with a */ SELECT id,name,email,phone,company,salary,department FROM person WHERE name LIKE 'a%' OR name LIKE 'A%'; -- or SELECT id,name,email,phone,company,salary,department FROM person WHERE name LIKE any (array['a%', 'A%']); -- or -- ILIKE operator matches the value case-insensitively SELECT id,name,email,phone,company,salary,department FROM person WHERE name ILIKE 'a%';
Once the sql script is executed successfully the names starting with the letter ‘a’ will be shown on the pgAdmin console.
2.4.3 Query 3
The third sql query will display the rows that have the letter ‘a’ between their names.
Query 3 snippet
/* %a% -> name having a in between */ SELECT id,name,email,phone,company,salary,department FROM person WHERE name LIKE '%a%';
Once the sql script is executed successfully the rows will be shown on the pgAdmin console.
2.5 Using underscore wildcard operator
The underscore (_) wildcard operator in sql is used to match any character. Let us understand this with the help of a sql query.
Query 1
SELECT id,name,email,phone,company,salary,department FROM person WHERE name LIKE '_a%';
Once the sql script is executed successfully the rows will be shown on the pgAdmin console.
3. Summary
In this tutorial, we learned the different SQL wildcard operators used in the postgresql database and saw their practical implementation. You can download the sql scripts and docker commands used in this tutorial from the Downloads section.
4. Download the Scripts
This was a tutorial on learning the SQL wildcard operators.
You can download the full source code of this example here: SQL Wildcard Operators
