Using PostgreSQL LIKE for Flexible Text Matching

PostgreSQL LIKE Operator: Pattern Matching in Queries

The LIKE operator in PostgreSQL is used for pattern matching within a query. It’s commonly employed when you want to find rows where a column's value fits a specified pattern, typically using wildcard characters like % and _. This is particularly useful for searches within text data, allowing you to filter results based on substrings or specific text patterns.

Syntax:

SELECT column_name(s)
FROM table_name
WHERE column_name LIKE 'pattern';

Wildcards in Patterns:

• % – Matches any sequence of characters (including an empty sequence).
• _ – Matches exactly one character.

Examples of Using LIKE in PostgreSQL

Example 1: Basic Pattern Matching with %

Suppose you want to find customers whose names start with "Jo":

Code:

SELECT * FROM customers
WHERE customer_name LIKE 'Jo%';

Explanation:

• This query fetches all rows from the customers table where the customer_name begins with "Jo".
• The % wildcard allows any sequence of characters to follow "Jo".

Example 2: Using _ for Single Character Matching

To find customers whose names are four letters long and begin with "Jo":

Code:

SELECT * FROM customers
WHERE customer_name LIKE 'Jo__';

Explanation:

• The two _ wildcards specify that exactly two more characters follow "Jo".
• This query would return results like "John" or "Joey" but not "Joelene".

Example 3: Combining LIKE with Other Conditions

You can also combine LIKE with other conditions using AND or OR operators. For example:

Code:

SELECT * FROM products
WHERE product_name LIKE '%box%'
AND price > 20;

Explanation:

• This query retrieves products whose product_name contains "box" and whose price is above 20.
• The % wildcard on both sides allows "box" to appear anywhere within the product name.

Case Sensitivity and Case-Insensitive Matching:

In PostgreSQL, LIKE is case-sensitive. For case-insensitive searches, use the ILIKE operator, which functions similarly to LIKE but ignores case.

Code:

SELECT * FROM customers
WHERE customer_name ILIKE 'jo%';

Explanation:

• This query retrieves any customer whose name begins with "jo", regardless of case (e.g., "John", "jones", "JOE").

Practical Applications of LIKE in PostgreSQL:

• Search Filters: Use LIKE to create search filters within applications, allowing users to find entries matching specific text patterns.
• Data Validation: Quickly validate data within tables by finding rows that match specific formats or substrings.
• Case-Insensitive Searches: Use ILIKE for matching patterns in a case-insensitive manner, useful in user-facing applications where case consistency might vary.

Summary:

The PostgreSQL LIKE operator is a versatile tool for pattern-based text searches. Combined with wildcards and conditional operators, it can be tailored for various search and filtering needs, while ILIKE provides a case-insensitive alternative. Understanding how to utilize LIKE and its wildcards enhances your ability to query and manipulate textual data effectively in PostgreSQL.

All PostgreSQL Questions, Answers, and Code Snippets Collection.