MySQL LIKE

The MySQL LIKE operator is used to search for a specified pattern in a column’s data. It is often used in conjunction with wildcard characters to perform pattern matching within text data. The LIKE operator is typically used in the WHERE clause of a SQL query to filter and retrieve rows that match a particular pattern.

The basic syntax of the LIKE operator is as follows:

SELECT columns
FROM table
WHERE column_name LIKE pattern;

Here, column_name is the name of the column you want to search, and pattern is the pattern you want to match. The pattern can include wildcard characters for more flexible matching:

• %: Matches any sequence of characters (including zero characters).
• _: Matches any single character.
• [charlist]: Matches any single character in the specified character list.
• [^charlist]: Matches any single character not in the specified character list.

Here are some examples to illustrate the use of the LIKE operator:

1. Match all records where the product_name starts with “App”:

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

2. Match all records where the customer_name contains the letter “A” as the second character:

   SELECT *
   FROM customers
   WHERE customer_name LIKE '_A%';

3. Match all records where the email ends with “@gmail.com”:

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

4. Match all records where the category contains either “Electronics” or “Appliances”:

   SELECT *
   FROM products
   WHERE category LIKE 'Electronics' OR category LIKE 'Appliances';

It’s important to note that the LIKE operator is case-sensitive by default. If you want to perform case-insensitive pattern matching, you can use the COLLATE keyword with a case-insensitive collation, such as utf8_general_ci. For example:

SELECT *
FROM products
WHERE product_name COLLATE utf8_general_ci LIKE 'app%';

The LIKE operator is a powerful tool for pattern matching in SQL queries, allowing you to search for data that matches a specific pattern or criteria within your database tables.