PostgreSQL: LIKE Operator – Pattern Matching Made Simple

Welcome to The Coding College, your ultimate resource for coding and programming tutorials! In this guide, we’ll explore the LIKE operator in PostgreSQL, a powerful tool for performing pattern-based searches in text data.

What is the LIKE Operator?

The LIKE operator is used in SQL queries to search for a specified pattern in a column. It’s commonly employed for filtering data based on substrings, prefixes, or suffixes.

Syntax

SELECT column_name
FROM table_name
WHERE column_name LIKE 'pattern';
  • column_name: The column being searched.
  • pattern: A string pattern with wildcards for matching.

Wildcards in LIKE

  1. %: Represents zero or more characters.
    • Example: 'A%' matches any string starting with A.
  2. _: Represents exactly one character.
    • Example: 'A_' matches any two-character string starting with A.

Example: Sample Table

Let’s consider a table named customers:

customer_idnamecityemail
1Alice JohnsonNew York[email protected]
2Bob SmithLos Angeles[email protected]
3Charlie BrownChicago[email protected]
4Diana PrinceNew York[email protected]

Using the LIKE Operator

Example 1: Find Customers from New York

SELECT name, city
FROM customers
WHERE city LIKE 'New%';

Result:

namecity
Alice JohnsonNew York
Diana PrinceNew York

Example 2: Find Customers Whose Name Starts with ‘B’

SELECT name
FROM customers
WHERE name LIKE 'B%';

Result:

name
Bob Smith

Example 3: Find Emails Containing ‘@gmail’

SELECT email
FROM customers
WHERE email LIKE '%@gmail%';

Result:

email
[email protected]

Example 4: Find Names with Exactly 5 Characters

SELECT name
FROM customers
WHERE name LIKE '_____';

Result:

name
Alice

Case-Insensitive Pattern Matching

In PostgreSQL, the ILIKE operator can be used for case-insensitive pattern matching.

Example 5: Case-Insensitive Search

SELECT name
FROM customers
WHERE name ILIKE 'b%';

Result:

name
Bob Smith

Real-World Applications

  1. Customer Filtering: Search customers by city, email domain, or name.
  2. Data Cleanup: Identify inconsistent patterns in records.
  3. Search Features: Implement basic search functionality in web apps.

Learn More at The Coding College

Visit The Coding College for more PostgreSQL tutorials and coding insights. We strive to deliver content that meets Google’s E-E-A-T standards, ensuring quality and user-centricity.

Conclusion

The PostgreSQL LIKE operator is a straightforward yet powerful tool for pattern-based queries. With wildcards and case-insensitive options, it can be adapted to various use cases.

Keep exploring The Coding College for more PostgreSQL tutorials and programming tips!

Leave a Comment