MySQL LIKE Clause

Overview

The LIKE operator is used for pattern matching in WHERE clauses. It searches for a specified pattern in a column, supporting wildcards for flexible matching.

LIKE Syntax

SELECT column1, column2
FROM table_name
WHERE column LIKE pattern;

Wildcard Characters

Percent (%)

-- Matches any sequence of characters
SELECT * FROM users WHERE name LIKE 'J%';         -- Starts with J
SELECT * FROM users WHERE email LIKE '%@gmail.com';  -- Ends with @gmail.com
SELECT * FROM products WHERE name LIKE '%laptop%';   -- Contains laptop

-- Matches zero or more characters
SELECT * FROM words WHERE word LIKE 'a%';        -- a, ab, abc, anything

Underscore (_)

-- Matches exactly one character
SELECT * FROM users WHERE name LIKE 'J_n';        -- Jan, Jen, Jqn
SELECT * FROM products WHERE code LIKE 'SKU_001'; -- SKU_001 only

-- Multiple underscores
SELECT * FROM codes WHERE value LIKE '__-__-____';  -- Pattern: XX-XX-XXXX

Combining Wildcards

-- Start with, end with
SELECT * FROM emails WHERE content LIKE '%important%message%';

-- Complex patterns
SELECT * FROM users WHERE phone LIKE '(___) ___-____';
SELECT * FROM products WHERE sku LIKE 'A___-%';

-- Escape wildcards
SELECT * FROM codes WHERE value LIKE '%\%%';     -- Contains %
SELECT * FROM texts WHERE content LIKE '%\_%';   -- Contains _

LIKE Patterns

Starts With

-- Names starting with A
SELECT * FROM users WHERE name LIKE 'A%';

-- Emails from specific domain
SELECT * FROM users WHERE email LIKE '@company.com';

-- Products in category
SELECT * FROM products WHERE category LIKE 'Electronics%';

Ends With

-- Files with specific extension
SELECT * FROM documents WHERE filename LIKE '%.pdf';

-- Users with specific suffix
SELECT * FROM users WHERE domain LIKE '%@example.org';

Contains

-- Products containing 'pro'
SELECT * FROM products WHERE name LIKE '%pro%';

-- Users with specific pattern
SELECT * FROM logs WHERE message LIKE '%error%';

-- Case-insensitive contains
SELECT * FROM articles WHERE title LIKE '%mysql%';

Specific Patterns

-- Exactly 5 characters
SELECT * FROM codes WHERE value LIKE '_____';

-- Starts with A, ends with Z
SELECT * FROM users WHERE name LIKE 'A%Z';

-- Contains exactly 3 characters
SELECT * FROM tags WHERE tag LIKE '___';

NOT LIKE / NOT LIKE

-- Does not start with
SELECT * FROM users WHERE name NOT LIKE 'A%';

-- Does not contain
SELECT * FROM products WHERE description NOT LIKE '%sale%';

-- Does not match pattern
SELECT * FROM emails WHERE sender NOT LIKE '%@spam.com%';

LIKE with Different Data Types

Strings

-- Text matching
SELECT * FROM users WHERE name LIKE '%Smith%';

-- Case sensitivity (MySQL LIKE is case-insensitive by default)
SELECT * FROM users WHERE name LIKE 'john%';  -- Matches John, JOHN, john

Numbers

-- Numbers as strings
SELECT * FROM phone_numbers WHERE phone LIKE '555-___';

-- ZIP codes
SELECT * FROM addresses WHERE zip LIKE '90%';

Dates

-- Dates as strings
SELECT * FROM logs WHERE date LIKE '2024-01-%';

-- Years
SELECT * FROM events WHERE year LIKE '202%';

ESCAPE Clause

Escape Default Character

-- Escape % character
SELECT * FROM codes WHERE value LIKE '%\%%' ESCAPE '\';

-- Escape underscore
SELECT * FROM texts WHERE content LIKE '%\_%' ESCAPE '\';

Custom Escape Character

-- Use different escape character
SELECT * FROM patterns WHERE value LIKE '%#%#%' ESCAPE '#';

-- Complex pattern with escape
SELECT * FROM codes WHERE value LIKE '%!%!%' ESCAPE '!';

LIKE in Different Contexts

UPDATE Statement

-- Update matching patterns
UPDATE users SET status = 'verified' 
WHERE email LIKE '%@company.com';

UPDATE products SET category = 'Updated' 
WHERE name LIKE '%Special%';

DELETE Statement

-- Delete matching patterns
DELETE FROM logs WHERE message LIKE '%deprecated%';

DELETE FROM temp_data WHERE session_id LIKE 'test_%';

INSERT Statement

-- Not typically used in INSERT, but in SELECT for insert
INSERT INTO processed_logs (message)
SELECT message FROM raw_logs WHERE message LIKE '%processed%';

Performance Considerations

Index Usage

-- LIKE with leading wildcard cannot use index
SELECT * FROM users WHERE email LIKE '%@gmail.com';  -- Full table scan

-- LIKE without leading wildcard CAN use index
SELECT * FROM users WHERE email LIKE 'john@%';      -- Can use index

-- Leading wildcard pattern
SELECT * FROM users WHERE name LIKE '%mith';        -- Full table scan

Optimization Strategies

-- Avoid leading wildcards when possible
-- Instead of: WHERE email LIKE '%@gmail.com'
-- Use: WHERE email REGEXP '@gmail.com$' (still slow, but more flexible)

-- Use FULLTEXT search for large text columns
CREATE FULLTEXT INDEX ft_content ON articles(title, content);
SELECT * FROM articles 
WHERE MATCH(title, content) AGAINST');

-- Consider('mysql tutorial storing reverse strings for trailing wildcard
SELECT * FROM users WHERE reversed_name LIKE '%htim';  -- For 'mith' ending

LIKE vs Other Pattern Matching

LIKE vs REGEXP

| Aspect |-------------|------|--------| | Wildcards | Performance | Flexibility | Leading wildcard | Use case

-- LIKE examples
SELECT * FROM users WHERE name LIKE 'J%';          -- Simple
SELECT * FROM users WHERE name LIKE '%ohn%';       -- Contains

-- REGEXP examples
SELECT * FROM users WHERE name REGEXP '^J';        -- Starts with J
SELECT * FROM users WHERE email REGEXP '^[a-z]+\.[a-z]+@';

LIKE vs IN

-- IN for exact matches
SELECT * FROM users WHERE country IN ('USA', 'UK', 'Canada');

-- LIKE for patterns
SELECT * FROM users WHERE country LIKE 'U%';  -- USA, UK, UAE, etc.

Practical Examples

Search Functionality

-- Simple search
SELECT * FROM products WHERE name LIKE '%search_term%';

-- Search with word boundaries
SELECT * FROM articles 
WHERE title LIKE '% mysql %' 
   OR title LIKE 'mysql %'
   OR title LIKE '% mysql'
   OR title = 'mysql';

-- Case-insensitive search
SELECT * FROM products 
WHERE LOWER(name) LIKE LOWER('%Search%');

Data Validation

-- Find invalid email patterns
SELECT * FROM users WHERE email NOT LIKE '%@%.%';

-- Find invalid phone formats
SELECT * FROM users WHERE phone NOT LIKE '(___) ___-____';

-- Find malformed codes
SELECT * FROM products WHERE sku NOT LIKE 'SKU-____-____';

Data Cleaning

-- Find duplicate-like entries
SELECT * FROM users WHERE name LIKE '% %';

-- Find entries with special characters
SELECT * FROM users WHERE name LIKE '%[^a-zA-Z0-9 ]%';

-- Find empty or whitespace-only
SELECT * FROM forms WHERE response LIKE '% %' = 0;

Summary

LIKE clause provides:

• Wildcards: % (any), _ (single)
• Pattern Matching: Simple string patterns
• Case Sensitivity: Default case-insensitive
• ESCAPE: Custom escape character
• Performance: Avoid leading wildcards
• Alternatives: REGEXP for complex patterns

---

Previous: DELETE

Next: UNION