Postgres like query

Updated May 24, 2023

Introduction to Postgres like query

Postgres, like the query, are used to match the text values from the pattern we have used in the query example; we can match the text values using the wildcards operator. If the search expression matches the given condition, which we have used with the like query, then the like query will return the true values, the true value is considered one. Like query or operator is most important and useful in PostgreSQL to match the text values. Two wildcards are available in PostgreSQL, with like operators, i.e., percent sign (%) and underscore (_).

Syntax of Postgres like query

Below is the syntax of the like query in PostgreSQL:

SELECT column_name FROM table_name WHERE column_name LIKE 'XX%'

Or

SELECT column_name FROM table_name WHERE column_name LIKE '%XX'

Or

SELECT column_name FROM table_name WHERE column_name LIKE '%XX%'

Or

SELECT column_name FROM table_name WHERE column_name LIKE 'XX_'

Or

SELECT column_name FROM table_name WHERE column_name LIKE '_XX'

Or

SELECT column_name FROM table_name WHERE column_name LIKE '_XX_'

Below is the parameter description syntax of a like query in PostgreSQL:

• Select: Select is used to select a column from the table which we have used like condition to fetch the data. We have to select all columns or specific columns.
• Column name: This is the column name from which we have used like query to match the text value from the pattern. We can use all or specific columns from the table.
• Table name: Table name specifies that we have to retrieve data using like query in PostgreSQL.
• Where: Where clause is used to specify a column from which we use like query condition in PostgreSQL. Where clause is very important while using like query in PostgreSQL.
• Like: We can use the like query in PostgreSQL to retrieve the data from the table while matching the text pattern using the wildcard operator.

How like query Work in PostgreSQL

Below is the working of like query in PostgreSQL:

We have to use a query to select, delete, update, and insert operations using where clauses and wildcard operators.

There are two types of wildcard operators used with like query in PostgreSQL.

• Percentage (%) wildcard
• Underscore (_) wildcard

1. Percentage (%) wildcard

• The percentage (%) sign represents one, zero, or multiple no of characters.
• Which we have used with a percentage in a different form that we have retrieving output.
• We can use percentage signs with multiple forms like query, i.e., ‘%xx’, ‘%xx%’, and ‘xx%’.

2. Underscore (_) wildcard

• The underscore (_) sign represents one number or character. We can combine numbers and characters with each other.
• We can use underscore signs with multiple forms in like query, i.e., ‘_xx’, ‘_xx_’, and ‘xx_’.
• If we have not used a like operator with an underscore and percentage sign, it will work as an equal operator in PostgreSQL.
• Like query is used when we cannot remember the string’s exact name. If the student table contains the column as a student name and we want to find the student name, starting and ending with a specific letter, we have used the same time as the query in PostgreSQL.
• We have also used like query when we have only remembered the half sting; using half string, we can find the whole string using like query in PostgreSQL.
• We can also use limit condition with a like query in PostgreSQL. We need to specify the case sensitive string while using like query in PostgreSQL.

Below is the example to show that we need to specify case sensitive strings while using like query in PostgreSQL:

Code:

select * from student where stud_name like '%abc%';
select * from student where stud_name like '%ABC%';

Output:

• In the first example, we have used a lowercase letter to match the string using a like query; same time, zero rows return because no match is available with the lowercase string name as “abc”.
• The query matches the pattern in the second example because we used the uppercase string name “ABC”.

Examples of Postgres like query

Below is an example of a like query in PostgreSQL:

We have used the student table to describe the example of a like query in PostgreSQL. The student table contains the below data.

Code:

select * from student;

Output:

Example #1

Like query using limit.

The example below shows that, like a query using the limit, we have used limit as 3 and percentage operator.

Code:

select * from student where stud_name like '%ABC%' limit 3;

Output:

Example #2

Like query using percentage wildcard operator.

The below example shows that like query using percentage wildcard operator.

a. Like query using ‘xx%.’

In the below example, we have to use ‘xx%’ using a like query as follows. We have to search all string that contains the ‘AB’ character.

Code:

select * from student where stud_name like 'AB%';

Output:

b. Like query using ‘%xx%.’

Code:

select * from student where stud_name like '%AB%';

Output:

c. Like query using ‘%xx.’

Code:

select * from student where stud_name like '%AB';

Output:

Example #3

Like query using underscore wildcard operator.

a. Like query using ‘xx_.’

Code:

select * from student where stud_name like 'PQ_';

Output:

b. Like query using ‘_xx.’

Code:

select * from student where stud_name like '_QR';

Output:

c. Like query using ‘_x_’

Code:

select * from student where stud_name like '_Q_';

Output:

d. Like query using ‘x__.’

Code:

select * from student where stud_name like 'P__';

Output:

We hope that this EDUCBA information on the “Postgres like query” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

1. PostgreSQL Integer
2. age() in PostgreSQL
3. PostgreSQL Link
4. PostgreSQL OR