LIKE Operator

The SQL LIKE Operator

In SQL, the LIKE operator is used in a WHERE clause to search for a specified pattern in a column. It's commonly used with the SELECT statement to filter rows based on a pattern match. The LIKE operator allows for the use of wildcard characters to represent unknown or variable characters in the pattern.

Here are the wildcard characters used with the LIKE operator:

• %: Represents zero, one, or multiple characters.
• _: Represents a single character.

Here's the basic syntax of the LIKE operator:

SELECT column1, column2, ...
FROM table_name
WHERE column_name LIKE pattern;

Now, let's look at some examples to understand how the LIKE operator works:

1. Using % wildcard:



SELECT *
FROM employees
WHERE first_name LIKE 'J%';

This query will return all rows from the employees table where the first_name starts with the letter 'J'.

2. Using _ wildcard:



SELECT * FROM employees WHERE last_name LIKE 'Sm_th';

This query will return all rows from the employees table where the last_name starts with 'Sm', followed by any single character, and ends with 'th'.

3. Using % and _ together:



SELECT *
FROM products
WHERE product_name LIKE 'C%er_';

This query will return all rows from the products table where the product_name starts with 'C', followed by zero or more characters, and ends with 'er', followed by any single character.

4. Using NOT LIKE operator:



SELECT * FROM customers WHERE email NOT LIKE '%@gmail.com';

This query will return all rows from the customers table where the email does not contain '@gmail.com'.

The LIKE operator is case-insensitive in some database systems like MySQL but case-sensitive in others like PostgreSQL. If you need case-insensitive matching, you can use functions like LOWER() or UPPER() to convert both the column value and the search pattern to the same case.