The LIKE operator in MySQL is used for pattern matching within string data. It allows you to search for records that match a specific pattern, making it useful for filtering results based on partial string matches. The LIKE operator is often used in the WHERE clause of a query.

Syntax

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

• column_name: The column to be searched.
• pattern: The pattern to match. It can include wildcard characters for flexible searching.

Wildcard Characters

The LIKE operator uses special wildcard characters to define the pattern:

1. Percentage (%)

   • Represents zero or more characters.
   • Allows for matching any sequence of characters, including an empty string.

   Examples:

   • '%abc%': Matches any string that contains 'abc' anywhere.
   • 'abc%': Matches any string that starts with 'abc'.
   • '%abc': Matches any string that ends with 'abc'.

2. Underscore (_)

   • Represents a single character.
   • Useful for matching a specific number of characters at certain positions.

   Examples:

   • 'a_b': Matches any three-character string where 'a' is the first character and 'b' is the last character, with any single character in between.
   • 'a__b': Matches any four-character string where 'a' is the first character, 'b' is the last character, and there are exactly two characters in between.

Example Queries

1. Find Names Starting with 'John'

   SELECT name
   FROM employees
   WHERE name LIKE 'John%';
   

   This query retrieves names that start with 'John', such as 'John Smith', 'John Doe'.

2. Find Email Addresses Containing 'example'

   SELECT email
   FROM users
   WHERE email LIKE '%example%';
   

   This query finds email addresses that contain 'example' anywhere in the string.

3. Find Product Codes Ending with '123'

   SELECT product_code
   FROM products
   WHERE product_code LIKE '%123';
   

   This query retrieves product codes that end with '123'.

4. Find Codes of Exactly Four Characters Where the First is 'A' and the Last is 'B'

   SELECT code
   FROM items
   WHERE code LIKE 'A__B';
   

   This query matches codes like 'A1B', 'A2B', etc.

Using Escaping with LIKE

To search for patterns that include the wildcard characters % or _, you need to escape them using an escape character. The escape character is defined by you and helps distinguish between literal characters and wildcard characters.

Syntax with Escape Character:

SELECT column_name
FROM table_name
WHERE column_name LIKE 'pattern' ESCAPE 'escape_char';

Example:

To search for the literal string '100%':

SELECT column_name
FROM table_name
WHERE column_name LIKE '100\%' ESCAPE '\';

Here, \ is used as the escape character to match the % symbol literally.

Performance Considerations

• Leading Wildcards: Patterns that start with % may lead to slower query performance because the database may not use indexes efficiently for such patterns. This can be particularly impactful on large datasets.
• Index Usage: Avoid using leading % if possible to benefit from index optimization.

Case Sensitivity

By default, the LIKE operator is case-insensitive in MySQL when used with non-binary strings. For case-sensitive searches, use the BINARY keyword to enforce case sensitivity:

SELECT name
FROM employees
WHERE BINARY name LIKE 'John%';