SQL LIKE for Pattern Matching

The SQL LIKE operator matches text against a pattern. The two main wildcards are % for zero or more characters and _ for exactly one character.

SELECT id, name, class, mark
FROM student
WHERE name LIKE '%John%';

This returns rows where John appears anywhere in the name value.

SQL LIKE Syntax Top ↑

SELECT column_list
FROM table_name
WHERE column_name LIKE pattern;

LIKE is normally used with text columns inside a WHERE clause.

Using % to Match Zero or More Characters Top ↑

The percent wildcard can match any number of characters, including zero characters.

Starts with John Top ↑

SELECT id, name, class, mark
FROM student
WHERE name LIKE 'John%';

This matches values beginning with John, such as John Deo and John Mike.

Ends with John Top ↑

SELECT id, name, class, mark
FROM student
WHERE name LIKE '%John';

This matches values ending with John, such as Alex John.

Contains John anywhere Top ↑

SELECT id, name, class, mark
FROM student
WHERE name LIKE '%John%';

Starts with A and ends with n Top ↑

SELECT id, name, class, mark
FROM student
WHERE name LIKE 'A%n';

One matching sample record is Alex John.

Using _ to Match Exactly One Character Top ↑

The underscore wildcard matches one character. Two underscores match exactly two characters.

If an account number contains five characters and must end with 044:

SELECT *
FROM account_master
WHERE acc_no LIKE '__044';

The first two positions can contain any single characters, but exactly two positions must appear before 044.

Combine % and _ Top ↑

To find names starting with C but not having h as the second character:

SELECT id, name
FROM student
WHERE name LIKE 'C%'
  AND name NOT LIKE 'Ch%';

Using NOT LIKE Top ↑

NOT LIKE keeps rows that do not match the pattern.

SELECT id, name, class, mark
FROM student
WHERE name NOT LIKE '%John%';

This excludes names containing John.

LIKE with AND and OR Top ↑

Combine LIKE expressions with AND or OR.

Both patterns must match Top ↑

SELECT id, name
FROM student
WHERE name LIKE '%a%'
  AND name LIKE '%e%';

Either pattern can match Top ↑

SELECT id, name
FROM student
WHERE name LIKE '%Alex%'
   OR name LIKE '%Deo%';

Search across multiple columns Top ↑

To match a keyword in either of two text columns:

SELECT *
FROM table_name
WHERE column1 LIKE '%keyword%'
   OR column2 LIKE '%keyword%';

Use AND instead of OR when the keyword must match both conditions.

Case Sensitivity of LIKE in MySQL Top ↑

LIKE is not inherently always case-insensitive or always case-sensitive. In MySQL, the result depends mainly on the collation used by the compared expression.

With a typical case-insensitive collation, these may match the same text:

SELECT id, name
FROM student
WHERE name LIKE '%john%';

For a binary comparison in MySQL, one option is:

SELECT id, name
FROM student
WHERE name LIKE BINARY '%John%';

Search for a Literal % or _ Character Top ↑

Because % and _ are wildcards, a search for those literal characters needs an escape convention.

For example, using ! as the escape character:

SELECT id, description
FROM product
WHERE description LIKE '%!%%' ESCAPE '!';

Here !% means a literal percent sign rather than the multi-character wildcard.

LIKE with CONCAT() Top ↑

LIKE can also compare a fixed string against a pattern built from a column value.

SELECT id, name
FROM student
WHERE 'Our best student is Mr John Deo of 5th Class'
      LIKE CONCAT('%', name, '%');

Only rows whose name value appears inside the supplied text will match.

CONCAT to join strings in a query

LIKE with PHP PDO Prepared Statements Top ↑

When the search text comes from a form, keep the SQL prepared and place the wildcard characters in the bound value.

<?php
require 'config.php';

$keyword=trim(
    $_GET['keyword'] ?? ''
);

$pattern='%'.$keyword.'%';

$stmt=$dbo->prepare(
    "SELECT id,name,class,mark
     FROM student
     WHERE name LIKE :pattern
     ORDER BY id"
);

$stmt->bindValue(
    ':pattern',
    $pattern,
    PDO::PARAM_STR
);

$stmt->execute();

while($row=$stmt->fetch(PDO::FETCH_ASSOC)){
    echo htmlspecialchars(
        (string)$row['name'],
        ENT_QUOTES,
        'Windows-1252'
    ).'<br>';
}

Prepared statements protect the SQL value. If users must search for literal % or _, escape those wildcard characters according to the search behavior your application intends.

LIKE Query with Python MySQL Top ↑

The same SQL pattern can be used from Python. Add the wildcard characters to the parameter value rather than building the user input directly into the SQL string.

import mysql.connector

db_connection = mysql.connector.connect(
    host='localhost',
    user='root',
    password='your_password',
    database='your_database'
)

keyword = 'John'
pattern = f'%{keyword}%'

query = """SELECT id, name, class, mark
           FROM student
           WHERE name LIKE %s
           ORDER BY id"""

cursor = db_connection.cursor()
cursor.execute(query, (pattern,))

for row in cursor:
    print(row)

cursor.close()
db_connection.close()

See the Python MySQL tutorial for the database-connection side. This SQL page remains focused on how the LIKE pattern itself works.

LIKE Performance Notes Top ↑

Pattern shape affects how easily an index can help a LIKE search.

• LIKE 'John%' has a fixed beginning and can be more index-friendly.
• LIKE '%John' starts with a wildcard.
• LIKE '%John%' starts with a wildcard and often requires more scanning on large datasets.
• Actual performance depends on indexes, collation, data distribution and the query plan.

For large text-search applications, LIKE may not be the best search technology. The right choice depends on whether you need simple prefix/substring matching, regular expressions, or full-text search.

LIKE vs REGEXP vs LOCATE Top ↑

Common SQL LIKE Mistakes Top ↑

Forgetting the wildcard Top ↑

LIKE 'John' has no wildcard and behaves like an exact pattern match. Use John%, %John, or %John% when partial matching is intended.

Confusing % and _ Top ↑

% can match any number of characters. _ matches exactly one character.

Assuming LIKE is always case-insensitive Top ↑

MySQL case behavior depends on collation and the expressions being compared.

Concatenating raw user input into SQL Top ↑

Use prepared statements. Put wildcard characters into the parameter value rather than constructing unsafe SQL text from form input.

Assuming leading-wildcard searches scale well Top ↑

Queries such as LIKE '%term%' can become expensive on large datasets. Check the execution plan and consider another search approach where appropriate.

Calling LIKE a replacement for REGEXP Top ↑

LIKE is intentionally simpler. REGEXP is useful when the required pattern cannot be expressed clearly with only % and _.

Related SQL Tutorials Top ↑

Frequently Asked Questions Top ↑

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author   Watch on YouTube

Connect: