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:
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:
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:
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:
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:
Practical Applications of LIKE in PostgreSQL:
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.
RetroSearch is an open source project built by @garambo | Open a GitHub Issue
Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo
HTML:
3.2
| Encoding:
UTF-8
| Version:
0.7.4