MySQL : LIKE Operator

In MySQL, the LIKE operator is used in the WHERE clause to search for a specified pattern in a column. It is commonly used with wildcard characters to perform pattern matching. Here's how it works:

Basic Syntax:


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

  • column1, column2, ...: The columns you want to retrieve data from.
  • table_name: The name of the table you want to retrieve data from.
  • column_name: The column in which you want to search for the pattern.
  • pattern: The pattern you want to search for. It can include wildcard characters to represent unknown parts of the pattern.

Wildcard Characters:

  • %: Represents zero or more characters.
  • _: Represents a single character.

Examples:

1. Basic Usage:
   
   SELECT * FROM employees WHERE last_name LIKE 'Sm%';
   
   This query will retrieve all employees whose last names start with 'Sm'.

2. Using `%` Wildcard:
   
   SELECT * FROM products WHERE product_name LIKE '%shirt%';
   
   This query will retrieve all products with the word 'shirt' anywhere in their name.

3. Using `_` Wildcard:
   
   SELECT * FROM users WHERE username LIKE 'user_';
   
   This query will retrieve all users with usernames consisting of six characters where the last character is 'r'.

4. Combining Wildcards:
   
   SELECT * FROM customers WHERE email LIKE 'john%doe@example.com';
   
   This query will retrieve customers with email addresses starting with 'john' and ending with '@example.com'.

5. Negating Wildcards:

   SELECT * FROM products WHERE product_name NOT LIKE '%apple%';

   This query will retrieve all products that do not have the word 'apple' anywhere in their name.

The LIKE operator is useful for performing flexible searches where you need to match patterns in your data. It's important to note that using LIKE with wildcard characters can lead to slower query performance compared to exact matches, especially on large datasets, so use it judiciously.