MySQL - Like Operator

MySQL: The LIKE Operator for Pattern Matching

The LIKE operator in MySQL is a powerful tool used in a WHERE clause to search for a specified pattern in a string column. While a simple = operator finds an exact match, LIKE allows for flexible searching using wildcard characters.

Syntax

SELECT column_name(s)
FROM table_name
WHERE column_name LIKE pattern;

LIKE Wildcards

MySQL’s LIKE operator supports two primary wildcard characters:

Wildcard	Description
%	The percent sign represents zero, one, or multiple characters.
_	The underscore represents exactly one single character.

**Important Note:** Unlike some other SQL dialects, the standard MySQL `LIKE` operator does not support bracket-style wildcards like `[abc]` or `[^abc]`. For that level of pattern matching, you should use MySQL's regular expression functions, such as `REGEXP_LIKE()`.

Practical Examples

Let’s use the employees table from our previous chapter. If you don’t have it, you can create and populate it with this script:

-- Create the employees table
CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    department VARCHAR(50),
    salary DECIMAL(10, 2)
);

-- Insert sample data
INSERT INTO employees (first_name, last_name) VALUES
('Ayla', 'Chen'), ('Ben', 'Carter'), ('Chloe', 'Davis'),
('David', 'Miller'), ('Jon', 'Smith'), ('Jen', 'Wilson');

1. Pattern at the End (%)

To find all employees whose first name starts with ‘Be’:

SELECT first_name, last_name FROM employees WHERE first_name LIKE 'Be%';

first_name	last_name
Ben	Carter

2. Pattern at the Beginning (%)

To find all employees whose last name ends with ‘er’:

SELECT first_name, last_name FROM employees WHERE last_name LIKE '%er';

first_name	last_name
Ben	Carter
David	Miller

3. Pattern in the Middle (%)

To find all employees whose last name contains ‘is’:

SELECT first_name, last_name FROM employees WHERE last_name LIKE '%is%';

first_name	last_name
Chloe	Davis

4. Single Character Wildcard (_)

To find all employees whose first name is three letters long and starts with ‘J’ and ends with ‘n’:

SELECT first_name, last_name FROM employees WHERE first_name LIKE 'J_n';

first_name	last_name
Jon	Smith
Jen	Wilson

Using NOT LIKE

You can use the NOT operator to find rows that do not match a pattern. For example, to find all employees whose first name does not start with ‘J’:

SELECT first_name, last_name FROM employees WHERE first_name NOT LIKE 'J%';

Searching for Wildcard Characters

What if you need to search for a literal ’%’ or ’_’ in your data? You can use the ESCAPE clause to specify an escape character. The character that follows the escape character in the pattern is treated as a literal character.

-- Let's imagine a table 'products' with a column 'discount_code'
-- To find codes that literally contain '50%', we can do:
SELECT product_name FROM products WHERE discount_code LIKE '%50\%%' ESCAPE '\';

Performance Considerations

Database indexes can dramatically speed up queries. However, a LIKE query can only use an index effectively if the pattern does not start with a wildcard.

• Good (Can use an index): WHERE name LIKE 'Smith%'
• Bad (Cannot use an index, requires full table scan): WHERE name LIKE '%Smith%'

For full-text search capabilities on large text fields, consider using MySQL’s Full-Text Search functions instead of LIKE.

Advanced Matching with REGEXP_LIKE()

For more complex pattern matching needs, MySQL 8.0+ provides the REGEXP_LIKE() function, which uses powerful regular expressions.

-- Find employees whose last name starts with C or D
SELECT first_name, last_name
FROM employees
WHERE REGEXP_LIKE(last_name, '^[CD]'); -- '^' means start of string, [CD] means C or D

Output:

first_name	last_name
Ayla	Chen
Ben	Carter
Chloe	Davis

Using LIKE in an Application (Python Example)

When using LIKE with user input, it’s critical to use prepared statements to prevent SQL injection. Here’s a Python example for building a simple employee search function.

import mysql.connector
import os
from dotenv import load_dotenv

load_dotenv()

def search_employees_by_last_name(search_term):
    """Searches for employees where the last name contains the search term."""
    results = []
    try:
        with mysql.connector.connect(
            host=os.getenv('DB_HOST'),
            user=os.getenv('DB_USER'),
            password=os.getenv('DB_PASSWORD'),
            database=os.getenv('DB_NAME')
        ) as conn:
            with conn.cursor(dictionary=True) as cursor:
                # The pattern is constructed to find any match containing the search term
                pattern = f"%{search_term}%"
                query = "SELECT id, first_name, last_name FROM employees WHERE last_name LIKE %s"

                # Pass the pattern as a parameter
                cursor.execute(query, (pattern,))
                results = cursor.fetchall()

    except mysql.connector.Error as e:
        print(f"Database error: {e}")
        return None

    return results

if __name__ == "__main__":
    user_search = 'er' # Simulating user input
    employees_found = search_employees_by_last_name(user_search)

    if employees_found is not None:
        print(f"Found {len(employees_found)} employee(s) matching '{user_search}':")
        for emp in employees_found:
            print(f"- ID: {emp['id']}, Name: {emp['first_name']} {emp['last_name']}")