MySQL LIKE

The MySQL LIKE operator is a powerful tool for searching and retrieving data from database tables based on pattern matching. It is commonly used in conjunction with the SELECT statement to filter rows that match a specified pattern within a specified column. The LIKE operator is particularly useful when you need to perform wildcard searches or match patterns within strings.

Syntax

Here is a basic syntax for using the LIKE operator:

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

The pattern parameter is the key to the LIKE operator’s functionality, and it can include wildcard characters to represent unknown or variable parts of the data. The two primary wildcard characters used with the LIKE operator are:

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

Examples

Let’s explore some examples to illustrate how the LIKE operator works:

Basic Pattern Matching:

SELECT * FROM employees
WHERE last_name LIKE 'Sm%';

This query retrieves all rows from the “employees” table where the last name starts with “Sm.”

Wildcard for Any Characters:

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

This query retrieves all rows from the “products” table where the product name contains the word “apple” anywhere within it.

Wildcard for Single Character:

SELECT * FROM customers
WHERE contact_name LIKE 'J_n%';

This query retrieves all rows from the “customers” table where the contact name starts with “J,” followed by any single character, and then followed by zero or more characters.

Combining Wildcards:

SELECT * FROM orders
WHERE order_date LIKE '2023-12%';

This query retrieves all rows from the “orders” table where the order date starts with “2023-12.”

It’s important to note that the LIKE operator is case-insensitive by default in MySQL. However, you can make it case-sensitive by using the COLLATE clause with a case-sensitive collation, such as COLLATE utf8_bin.

In summary, the MySQL LIKE operator is a valuable tool for pattern matching in SQL queries, allowing you to retrieve specific data based on flexible and dynamic criteria within specified columns. It’s commonly used for text-based searches and filtering operations in database applications.