LIKE pattern matching in MySQL

Introduction

LIKE helps you find text in a database when you only know part of it or want to match a pattern.

You want to find all names that start with 'A'.

You want to find all emails that contain 'gmail'.

You want to find all products with 'book' anywhere in their name.

You want to find all cities that end with 'ville'.

Syntax

MySQL

SELECT column_name FROM table_name WHERE column_name LIKE 'pattern';

Use % to match any number of characters (including zero).

Use _ to match exactly one character.

Examples

Finds all names starting with 'A'.

MySQL

SELECT name FROM users WHERE name LIKE 'A%';

Finds all emails containing 'gmail' anywhere.

MySQL

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

Finds all cities ending with 'ville'.

MySQL

SELECT city FROM locations WHERE city LIKE '%ville';

Finds all codes starting with 'AB' and any one character after.

MySQL

SELECT code FROM products WHERE code LIKE 'AB_';

Sample Program

This creates a table of employees, adds some names, and finds all names starting with 'Al'.

MySQL

CREATE TABLE employees (id INT, name VARCHAR(50));
INSERT INTO employees VALUES (1, 'Alice'), (2, 'Bob'), (3, 'Alfred'), (4, 'Albert'), (5, 'Bobby');
SELECT name FROM employees WHERE name LIKE 'Al%';

Important Notes

LIKE is case-insensitive in MySQL by default, but this depends on the collation.

Using % at the start of the pattern can slow down the search because it can't use indexes well.

Use LIKE when you want simple pattern matching; for complex patterns, consider REGEXP.

Summary

LIKE helps find text matching simple patterns using % and _.

Use % for any number of characters, _ for exactly one character.

It is useful for searching partial text in database columns.