Introduction to Wildcards in PostgreSQL
Wildcards in PostgreSQL is used to find matching rows values from tables; it is also used to find matching patterns rows from tables, Wildcards is also used to find matching rows, column and tables names; the output of the wildcard operator will return matching name, which was table name, column name or rows, In PostgreSQL like operator is used to match text values against a pattern using wildcards, If search expression that we have using in query can be matched to the pattern expression, wildcard operator is essential in PostgreSQL.
Explain all Wildcards in PostgreSQL
We will explain all wildcards in PostgreSQL one by one are as follows. The following are the wildcard operatory used in PostgreSQL.
- Using percentage (%) wildcard
- Using underscore (_) wildcard
1. Using % Wildcard
- PostgreSQL wildcard is used to match text values from matching patterns. Like operator is used to find matching text values from the table.
- There are two types of wildcard operators used in PostgreSQL.
- Percentage (%) wildcard
- Underscore (_) wildcard
- The percentage (%) sign represents one, zero or multiple no of characters, which have used percentage in a different form to retrieve the output.
- We have using the employee table to describe the wildcard operator in PostgreSQL.
Table Example
testing=# CREATE TABLE Employee ( emp_id INT NOT NULL, emp_name character(10) NOT NULL, emp_address character(20) NOT NULL, emp_phone character(14), emp_salary INT NOT NULL, PRIMARY KEY (emp_name));
testing=# INSERT INTO Employee (emp_id, emp_name, emp_address, emp_phone, emp_salary) VALUES (1, 'ABC', 'Pune', '1234567890', 20000);
testing=# INSERT INTO Employee (emp_id, emp_name, emp_address, emp_phone, emp_salary) VALUES (1, 'PQR', 'Pune', '1234567890', 20000);
testing=# INSERT INTO Employee (emp_id, emp_name, emp_address, emp_phone, emp_salary) VALUES (1, 'XYZ', 'Mumbai', '1234567890', 35000);
testing=# INSERT INTO Employee (emp_id, emp_name, emp_address, emp_phone, emp_salary) VALUES (2, 'BBS', 'Mumbai', '1234567890', 45000);
testing=# INSERT INTO Employee (emp_id, emp_name, emp_address, emp_phone, emp_salary) VALUES (2, 'RBS', 'Delhi', '1234567890', 50000);
Output:
Syntax
Please find below syntax of percentage (%) wildcard operator are as follows.
- %xxx% : Select column_name1, …, column_nameN from table_name where column_name like ‘%xxx%’;
- xxx% : Select column_name1, …, column_nameN from table_name where column_name like ‘xxx%’;
- %xxx : Select column_name1, …, column_nameN from table_name where column_name like ‘%xxx’;
- xxx : Select column_name1, …, column_nameN from table_name where column_name like ‘xxx’;
Please find below the parameter description of the above syntax are as follows.
- Column1 to columnN – Column is used in the query to fetch data from the table.
- Table name – Table name used in the query to fetch data from that table.
- Percentage (%) – Percentage wildcard (%) operator used to fetch matching data from the table.
- Like – Like operator is used to fetch data from the table.
- Where – Where clause is used to fetch data from the table.
Example
Please find below an example of the percentage (%) wildcard operator is as follows.
1. Like ‘%xxxx%.’
The below example returns all rows that matching Pune text from the employee address from the employee table.
testing=# select emp_id, emp_name, emp_address, emp_salary from employee where emp_address like '%Pune%';
Output:
2. Like ‘%xx.’
The below example returns all rows which matching “un” text from the employee address from the employee table.
Which starts matching patterns from the right side.
There are no matching rows in the below query, so that it will return an empty set.
testing=# select emp_id, emp_name, emp_address, emp_salary from employee where emp_address like '%un'
Output:
3. Like ‘xx%.’
The below example returns all rows which matching “un” text from the employee address from the employee table.
Which starts matching patterns from the left side.
There are no matching rows in the below query, so that it will return an empty set.
testing=# select emp_id, emp_name, emp_address, emp_salary from employee where emp_address like 'un%';
Output:
4. Like ‘xxx.’
The below example returns all rows which matching “une” text from the employee address from the employee table.
Which starts matching patterns from both the side.
There is no matching in the below query, so that it will return an empty set.
testing=# select emp_id, emp_name, emp_address, emp_salary from employee where emp_address like 'une';
Output:
Using the underscore (_) operator
Please find below syntax and example of underscore (_) wildcard operators.
Syntax
Please find below syntax of Underscore (_) wildcard operator are as follows.
- _xxx_ : Select column_name1, …, column_nameN from table_name where column_name like ‘_xxx_’;
- xxx_ : Select column_name1, …, column_nameN from table_name where column_name like ‘xxx_’;
- _xxx : Select column_name1, …, column_nameN from table_name where column_name like ‘_xxx’;
- xxx : Select column_name1, …, column_nameN from table_name where column_name like ‘xxx’;
Please find parameter description of above syntax are as follows.
- Column1 to columnN – Column is used to fetch data from a query.
- Table name – Table name used in the query to fetch data from a table.
- Underscore (_) – Underscore wildcard (_) operator is used to fetch matching data from the table.
- Like – Like operator is used to fetch data from the table.
- Where – Where clause is used to fetch data from the table.
Example
Here are the following examples mention below
1. Like ‘_xxx_.’
Below is the example of like ‘_xxx_’ wildcard operator is as follows.
testing=# select emp_id, emp_name, emp_address, emp_salary from employee where emp_salary::text like '_000_';
Output:
2. Like ‘_xxxx.’
Below is the example of like ‘_xxxx’ wildcard operator is as follows.
testing=# select emp_id, emp_name, emp_address, emp_salary from employee where emp_salary::text like '_0000';
Output:
3. Like ‘x____.’
Below is the example of like ‘x____’ wildcard operator is as follows.
testing=# select emp_id, emp_name, emp_address, emp_salary from employee where emp_salary::text like '2____';
Output:
4. Like ‘_____.’
Below is the example of like ‘_____’ wildcard operator is as follows.
testing=# select emp_id, emp_name, emp_address, emp_salary from employee where emp_salary::text like '_____';
Output:
Conclusion
Wildcard operator is essential in PostgreSQL. Basically, we used percentage (%) and underscore (_) wildcard operator to fetch matching rows from the table. There are many ways to retrieve data from the table using wildcard operator like (‘%xxx%’, ‘%xxx’, ‘xxx%’, ‘xxx’, ‘____’, ‘_xxx’, ‘xxx_’ and ‘_xx_’).
Recommended Articles
This is a guide to Wildcards in PostgreSQL. Here we discuss the explanation of all Wildcards in PostgreSQL along with the examples. You can also refer to our other related articles to learn more –
