Postgres like query

Introduction to Postgres like query

Postgres, like the query, are basically 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 like query, then 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. There are two wildcards available in PostgreSQL, which was used with like operator, i.e. percent sign (%) and underscore (_).

Syntax of Postgres like query

Below is the syntax of 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 like query in PostgreSQL:

• Select: Select is used to select a column from the table which we have use like condition to fetch the data. We have select all column or specific column.
• Column name: This is the column name from which we have use like query to match the text value from the pattern. We can use all or specific column from the table.
• Table name: Table name specifies that from which we have to retrieve data using like query in PostgreSQL.
• Where: Where clause is used to specify a column from which we have using 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 operator 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 sign with multiple forms in like query, i.e. ‘%xx’, ‘%xx%’ and ‘xx%’.

2. Underscore (_) wildcard

• The underscore (_) sign represents one number or characters. We can combine number and characters with each other.
• We can use underscore sign 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 have not remember the exact name of the string. If the student table contained the column as a student name and we want to find the student name, which was start and end 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 string 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 string using like query, same time zero rows return because of no match available with the lowercase string name as “abc”.
• In the second example, like the query is matched with the pattern because we have used the uppercase string name as “ABC”.

Examples of Postgres like query

Below is the 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 below example shows that like a query using the limit, we have used limit as 3 also used 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 like query as follows. We have to search all string which contains ‘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:

This is a guide to Postgres like query. Here we discuss the introduction, how like query work in PostgreSQL and examples, respectively. You may also have a look at the following articles to learn more –

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