MySQL WHERE with LIKE - Pattern Matching in SQL

LIKE Syntax

SELECT columns FROM table
WHERE column LIKE pattern;

Wildcard Characters

Common Patterns

-- Starts with "php"
SELECT * FROM tutorials WHERE title LIKE 'php%';

-- Ends with "guide"
SELECT * FROM tutorials WHERE title LIKE '%guide';

-- Contains "javascript"
SELECT * FROM tutorials WHERE title LIKE '%javascript%';

-- Second character is "a"
SELECT * FROM tutorials WHERE title LIKE '_a%';

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

-- Starts with "A" and ends with "s"
SELECT * FROM tutorials WHERE title LIKE 'A%s';

-- NOT LIKE - exclude matches
SELECT * FROM tutorials WHERE title NOT LIKE '%deprecated%';

Case Sensitivity

In MySQL, LIKE is case-insensitive by default when using a case-insensitive collation (like utf8mb4_general_ci). To force case-sensitive matching:

-- Force case-sensitive with BINARY
SELECT * FROM users WHERE name LIKE BINARY 'John%';

-- Or use COLLATE
SELECT * FROM users
WHERE name LIKE 'John%' COLLATE utf8mb4_bin;

Escaping Special Characters

-- Search for literal % or _ using ESCAPE
SELECT * FROM products
WHERE discount LIKE '%10\%%';  -- finds "10%"

-- Custom escape character
SELECT * FROM logs
WHERE message LIKE '%file\_name%' ESCAPE '\';

PHP MySQLi Example

<?php
$conn = new mysqli('localhost', 'user', 'pass', 'db');

// Always use prepared statements to prevent SQL injection
$search = '%' . $conn->real_escape_string($_GET['q']) . '%';

$stmt = $conn->prepare(
  "SELECT id, title, category FROM tutorials WHERE title LIKE ?"
);
$stmt->bind_param('s', $search);
$stmt->execute();
$result = $stmt->get_result();

while ($row = $result->fetch_assoc()) {
  echo "<p>{$row['title']} -{$row['category']}</p>";
}

$stmt->close();
$conn->close();

PHP PDO Example

<?php
$pdo = new PDO('mysql:host=localhost;dbname=mydb', 'user', 'pass');

$search = '%' . $_GET['q'] . '%';
$stmt = $pdo->prepare(
  "SELECT * FROM tutorials WHERE title LIKE :search"
);
$stmt->execute(['search' => $search]);
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

Performance Tips

LIKE queries starting with % cannot use indexes and trigger a full table scan. For large tables, consider these alternatives:

-- SLOW: leading wildcard - full table scan
SELECT * FROM articles WHERE body LIKE '%javascript%';

-- FAST: no leading wildcard - uses index
SELECT * FROM articles WHERE title LIKE 'javascript%';

-- BETTER: Full-text search for large text columns
ALTER TABLE articles ADD FULLTEXT(title, body);
SELECT * FROM articles
WHERE MATCH(title, body) AGAINST('javascript' IN BOOLEAN MODE);