Introduction

In this video, we will learn about the LIKE and ILIKE operators in PostgreSQL. These operators are used to perform pattern matching on string values. They allow you to search for specific patterns or use wildcards to match multiple characters.

LIKE Operator

The LIKE operator is used to match a string pattern. It is case-sensitive, meaning it will only match the exact case of the pattern you specify. The basic syntax is:

SELECT column1, column2, ...
FROM table_name
WHERE column LIKE 'pattern';

The pattern can include two wildcard characters: - % matches any sequence of zero or more characters - _ matches any single character For example:

SELECT *
FROM products
WHERE product_name LIKE 'iPhone%';

This will return all rows from the products table where the product_name starts with 'iPhone'.

ILIKE Operator

The ILIKE operator is similar to LIKE, but it is case-insensitive. It will match the pattern regardless of the case of the string. The basic syntax is:

SELECT column1, column2, ...
FROM table_name
WHERE column ILIKE 'pattern';

For example:

SELECT *
FROM customers
WHERE email ILIKE '%@example.com';

This will return all rows from the customers table where the email ends with '@example.com', regardless of the case of the email address.

Examples

Let's look at some more examples of using LIKE and ILIKE:

SELECT *
FROM employees
WHERE first_name LIKE 'J%' AND last_name LIKE 'S%';

This will return all rows from the employees table where the first_name starts with 'J' and the last_name starts with 'S'.

SELECT *
FROM orders
WHERE order_date BETWEEN '2022-01-01' AND '2022-12-31'
  AND ship_address LIKE '%New York%';

This will return all rows from the orders table where the order_date is in 2022 and the ship_address contains 'New York'.

SELECT *
FROM products
WHERE product_name ILIKE '%shirt%'
  AND category IN ('Clothing', 'Accessories');

This will return all rows from the products table where the product_name contains 'shirt' (case-insensitive) and the category is 'Clothing' or 'Accessories'.

Conclusion

In this video, we covered the LIKE and ILIKE operators in PostgreSQL. These operators allow you to perform pattern matching on string values using wildcards. LIKE is case-sensitive, while ILIKE is case-insensitive. Practice using LIKE and ILIKE in your queries to become more comfortable with these operators.