MySQL Wildcards

Wildcard Characters

LIKE 'A%'
-- A, AB, ABC, ABCD...

LIKE 'A_'
-- AB, AC, A1, A2...

LIKE 'A_%'
-- AB, ABC, ABCD...

LIKE '%a%e%'
-- Contains a then e

🔹 The % Wildcard

The percent sign (%) is the most versatile wildcard, representing zero, one, or any number of characters. Position it strategically: at the start for suffix matching, at the end for prefix matching, or on both sides for substring matching anywhere.

-- Starts with 'Tech'
SELECT * FROM products
WHERE name LIKE 'Tech%';
-- Matches: Tech, Technology, Technical, TechPro

-- Ends with 'Pro'
SELECT * FROM products
WHERE name LIKE '%Pro';
-- Matches: Pro, MacPro, SurfacePro, AirPods Pro

-- Contains 'smart'
SELECT * FROM products
WHERE name LIKE '%smart%';
-- Matches: Smartphone, Smart TV, SmartWatch

-- Empty string also matches
SELECT * FROM items
WHERE code LIKE '%';
-- Matches all records (% can be zero characters)

🔹 The _ Wildcard

The underscore (_) matches exactly one character at a specific position. Each underscore represents one mandatory character. Use multiple underscores for fixed-length patterns like codes, IDs, or formatted data where you know the exact length but not all characters.

-- Exactly 3 characters starting with 'A'
SELECT * FROM codes
WHERE code LIKE 'A__';
-- Matches: ABC, AXY, A12 (but not A, AB, or ABCD)

-- 4 characters, second is 'o'
SELECT * FROM words
WHERE word LIKE '_o__';
-- Matches: book, door, food, cool

-- Phone pattern: 555-1234
SELECT * FROM contacts
WHERE phone LIKE '555-____';
-- Matches: 555-1234, 555-5678, 555-9999

-- Exactly 5 characters
SELECT * FROM items
WHERE item_code LIKE '_____';
-- Matches any 5-character code

🔹 Combining % and _

Mix both wildcards to create sophisticated search patterns with precise control. Use _ for specific positions where you need exactly one character, and % for variable-length sections. This combination is powerful for complex pattern matching in real-world data.

-- At least 3 chars, starts with 'A', ends with 'n'
SELECT * FROM names
WHERE name LIKE 'A_%_n';
-- Matches: Aaron, Alison, American (min 3 chars)

-- Second char is 'a', contains 'tech'
SELECT * FROM products
WHERE name LIKE '_a%tech%';
-- Matches: DataTech, Gateway Technology

-- Email: 3+ chars before @, any domain
SELECT * FROM users
WHERE email LIKE '___%@%.%';
-- Matches: [email protected], [email protected]

-- Product code: Letter + 3 digits + Letter
SELECT * FROM inventory
WHERE sku LIKE '_[0-9][0-9][0-9]_';
-- Pattern: A123B, X456Y

-- Starts with vowel, ends with 'ing'
SELECT * FROM verbs
WHERE word LIKE '[aeiou]%ing';
-- Matches: eating, opening, using

🔹 Wildcard Position Matters

Where you place wildcards dramatically changes what you match. Understanding positioning is crucial for accurate searches. Leading wildcards can slow queries on large tables, so use them wisely and consider indexes for frequently searched columns.

-- Different positions, different results

-- Prefix search (fastest with index)
WHERE name LIKE 'John%'
-- Matches: John, Johnson, Johnny

-- Suffix search
WHERE name LIKE '%son'
-- Matches: Johnson, Wilson, Anderson

-- Contains (slowest)
WHERE name LIKE '%oh%'
-- Matches: John, Kohler, Mahoney

-- Exact length with wildcards
WHERE code LIKE '___'
-- Matches: ABC, XYZ, 123 (exactly 3 chars)

-- Multiple wildcards
WHERE email LIKE '%@gmail.%'
-- Matches: [email protected], [email protected]

🔹 Escaping Wildcards

When you need to search for actual % or _ characters in your data, escape them using a backslash (\). This tells MySQL to treat them as literal characters instead of wildcards. Essential when searching for data that contains these special characters.

-- Search for actual % character
SELECT * FROM products
WHERE discount LIKE '50\%';
-- Finds products with "50%" in discount field

-- Search for actual _ character
SELECT * FROM files
WHERE filename LIKE 'report\_%';
-- Finds: report_2024, report_final

-- Search for both wildcards as literals
SELECT * FROM descriptions
WHERE text LIKE '%100\% \_guaranteed%';
-- Finds: "100% _guaranteed satisfaction"

-- Using ESCAPE clause (alternative method)
SELECT * FROM data
WHERE value LIKE '50!%' ESCAPE '!';
-- ! becomes the escape character

🔹 Common Wildcard Patterns

Here are frequently used wildcard patterns for typical database search scenarios:

-- Email validation pattern
WHERE email LIKE '%@%.%'

-- Phone number patterns
WHERE phone LIKE '___-___-____'  -- 555-123-4567
WHERE phone LIKE '(___) ___-____'  -- (555) 123-4567

-- Date patterns (as text)
WHERE date_string LIKE '____-__-__'  -- 2024-01-15

-- URL patterns
WHERE url LIKE 'https://%'
WHERE url LIKE '%.com'

-- File extensions
WHERE filename LIKE '%.pdf'
WHERE filename LIKE '%.jpg' OR filename LIKE '%.png'

-- Postal codes
WHERE zipcode LIKE '_____'  -- US 5-digit
WHERE postal LIKE '___ ___'  -- Canada A1A 1A1

-- Product codes
WHERE sku LIKE 'PROD-____-__'  -- PROD-1234-AB

-- Search multiple keywords
WHERE description LIKE '%laptop%'
   OR description LIKE '%computer%'
   OR description LIKE '%notebook%'

🔹 Practical Examples

Real-world wildcard usage in common database operations and search features:

-- Customer search by name or email
SELECT customer_id, name, email, phone
FROM customers
WHERE name LIKE '%smith%'
   OR email LIKE '%smith%'
ORDER BY name;

-- Product search with multiple criteria
SELECT product_name, category, price
FROM products
WHERE (product_name LIKE '%laptop%' 
   OR product_name LIKE '%notebook%')
   AND category LIKE 'Electronics%'
ORDER BY price DESC;

-- Find incomplete records
SELECT * FROM users
WHERE email NOT LIKE '%@%.%'
   OR phone NOT LIKE '___-___-____';

-- Search blog posts by tags
SELECT title, author, published_date
FROM blog_posts
WHERE tags LIKE '%mysql%'
   OR tags LIKE '%database%'
   OR tags LIKE '%sql%'
ORDER BY published_date DESC
LIMIT 10;

-- Find files by extension
SELECT filename, size, upload_date
FROM files
WHERE filename LIKE '%.pdf'
   OR filename LIKE '%.doc%'
ORDER BY upload_date DESC;