PostgreSQL – LIKE Operator
The LIKE operator is used to perform pattern matching on string values. It allows you to retrieve rows from a table where a particular column’s value matches a specified pattern. The LIKE operator is commonly used with wildcard characters to perform flexible searches. The basic syntax of the LIKE operator is as follows:
SELECT column1, column2, ... FROM table_name WHERE column_name LIKE pattern;Here are the wildcard characters you can use with the LIKE operator:
%: Represents zero or more characters.
_: Represents a single character.
Here’s an example to illustrate how to use the LIKE operator:
Consider a table named customers with columns id, name, and email. Query to retrieve all customers whose email addresses end with “@example.com” is as below:
SELECT id, name, email FROM customers WHERE email LIKE '%@example.com';In this query, the % wildcard at the beginning of the pattern allows for any number of characters before “@example.com”. This effectively matches email addresses that end with “@example.com”.
If you want to retrieve customers with email addresses that have exactly five characters before “@example.com”, you can use the _ wildcard:
SELECT id, name, email FROM customers WHERE email LIKE '____@example.com';In this query, each _ represents a single character, so the pattern matches email addresses with exactly five characters before “@example.com”.
You can also use the LIKE operator to match patterns at the beginning or within a string:
LIKE 'pattern%': Matches strings that start with “pattern”.LIKE '%pattern': Matches strings that end with “pattern”.LIKE '%pattern%': Matches strings that contain “pattern” anywhere.
The LIKE operator is a powerful tool for performing flexible string pattern matching in your SQL queries, allowing you to retrieve rows based on specific text patterns in a column.