MySQL LIKE

Key LIKE Concepts

WHERE name LIKE 'A%'
-- Starts with A

WHERE code LIKE 'A_C'
-- A + any char + C

WHERE email LIKE '%@gmail%'
-- Contains @gmail

WHERE name NOT LIKE 'A%'
-- Doesn't start with A

🔹 Basic LIKE Syntax

The LIKE operator is used in the WHERE clause to search for patterns in text columns. It's case-insensitive by default in MySQL. Combine LIKE with wildcards (% and _) to create flexible search patterns for partial matches.

-- Basic syntax
SELECT column_name
FROM table_name
WHERE column_name LIKE pattern;

-- Example: Find names starting with 'J'
SELECT name, email
FROM customers
WHERE name LIKE 'J%';

🔹 Using % Wildcard

The percent sign (%) represents zero, one, or multiple characters. Place it at the beginning to match endings, at the end to match beginnings, or on both sides to match text anywhere in the string. It's the most flexible wildcard.

-- Starts with 'A'
SELECT * FROM products
WHERE product_name LIKE 'A%';

-- Ends with 'son'
SELECT * FROM customers
WHERE name LIKE '%son';

-- Contains 'tech' anywhere
SELECT * FROM products
WHERE description LIKE '%tech%';

-- Starts with 'S' and ends with 'n'
SELECT * FROM cities
WHERE city_name LIKE 'S%n';

🔹 Using _ Wildcard

The underscore (_) matches exactly one character at that position. Use multiple underscores to match multiple specific positions. This is useful for fixed-length codes, phone numbers, or when you know the exact length of the pattern you're searching for.

-- Match 3-letter codes starting with 'A'
SELECT * FROM products
WHERE code LIKE 'A__';

-- Match phone numbers like 555-1234
SELECT * FROM contacts
WHERE phone LIKE '555-____';

-- Second letter is 'a'
SELECT * FROM words
WHERE word LIKE '_a%';

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

🔹 Combining Wildcards

Mix % and _ wildcards to create precise search patterns. This gives you fine control over your searches, allowing you to specify exact positions while keeping other parts flexible. Useful for complex pattern matching like partial codes or formatted data.

-- Starts with 'A', has at least 3 chars, ends with 'n'
SELECT * FROM names
WHERE name LIKE 'A_%_n';

-- Second character is 'o', contains 'tech'
SELECT * FROM products
WHERE name LIKE '_o%tech%';

-- 5 characters, starts with 'P', ends with 'T'
SELECT * FROM codes
WHERE code LIKE 'P___T';

-- Email pattern: [email protected]
SELECT * FROM users
WHERE email LIKE '%@%.com';

🔹 NOT LIKE Operator

NOT LIKE excludes rows that match the pattern. This is useful for filtering out unwanted data, such as excluding test accounts, removing specific domains, or finding records that don't follow a certain naming convention or format.

-- Exclude names starting with 'Test'
SELECT * FROM customers
WHERE name NOT LIKE 'Test%';

-- Exclude Gmail addresses
SELECT * FROM users
WHERE email NOT LIKE '%@gmail.com';

-- Exclude products containing 'old'
SELECT * FROM products
WHERE product_name NOT LIKE '%old%';

-- Exclude 3-letter codes
SELECT * FROM items
WHERE code NOT LIKE '___';

🔹 Case Sensitivity

By default, LIKE is case-insensitive in MySQL. To make it case-sensitive, use the BINARY keyword before the pattern. This forces exact character matching including uppercase and lowercase distinctions, useful for passwords, codes, or when case matters in your data.

-- Case-insensitive (default)
SELECT * FROM products
WHERE name LIKE 'laptop%';  -- Matches 'Laptop', 'LAPTOP', 'laptop'

-- Case-sensitive search
SELECT * FROM products
WHERE name LIKE BINARY 'Laptop%';  -- Only matches 'Laptop...'

-- Case-sensitive with underscore
SELECT * FROM codes
WHERE code LIKE BINARY 'A__';

🔹 Practical Examples

Real-world applications of LIKE operator in common database queries and search features:

-- Search customers by partial name
SELECT customer_id, name, email
FROM customers
WHERE name LIKE '%smith%'
   OR email LIKE '%smith%';

-- Find products in specific category
SELECT product_name, price
FROM products
WHERE category LIKE 'Electronics%'
ORDER BY price DESC;

-- Search blog posts by keyword
SELECT title, author, published_date
FROM blog_posts
WHERE title LIKE '%mysql%'
   OR content LIKE '%mysql%'
ORDER BY published_date DESC;

-- Find phone numbers with area code
SELECT name, phone
FROM contacts
WHERE phone LIKE '555-%'
ORDER BY name;

-- Search addresses by city
SELECT customer_name, address
FROM customers
WHERE address LIKE '%New York%'
   OR address LIKE '%NY%';