EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Data Science Data Science Tutorials PostgreSQL Tutorial Postgres like query
 

Postgres like query

Updated May 24, 2023

Postgres like query

 

 

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 (_).

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

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:

postgres like query 1

  • 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:

postgres like query 2

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:

postgres like query 3

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:

postgres like query 4

b. Like query using ‘%xx%.’

Code:

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

Output:

using ‘%xx%’

c. Like query using ‘%xx.’

Code:

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

Output:

postgres like query 6

Example #3

Like query using underscore wildcard operator.

a. Like query using ‘xx_.’

Code:

select * from student where stud_name like 'PQ_';

Output:

using underscore wildcard operator

b. Like query using ‘_xx.’

Code:

select * from student where stud_name like '_QR';

Output:

using underscore wildcard operator

c. Like query using ‘_x_’

Code:

select * from student where stud_name like '_Q_';

Output:

postgres like query 11

d. Like query using ‘x__.’

Code:

select * from student where stud_name like 'P__';

Output:

postgres like query 12

Recommended Articles

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

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA Login

Forgot Password?

🚀 Limited Time Offer! - 🎁 ENROLL NOW