PostgreSQL – LIKE Operator

ByAdmin

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.

Similar Posts