How to Perform a Case-Sensitive LIKE Search in MySQL

The MySQL LIKE operator is commonly used to search for a specific pattern within a string. It is useful when you need to find values that begin with, end with, or contain a particular sequence of characters.

By default, whether LIKE is case-sensitive depends on the collation of the string column. With commonly used case-insensitive collations, a query such as:

SELECT*FROM athletesWHERE team LIKE'%avs';

can match:

MavsmavsMAVSCAVS

If you specifically need a case-sensitive pattern match, one simple MySQL approach is to use BINARY:

SELECT*FROM athletesWHERE team LIKEBINARY'%avs';

This makes the comparison case-sensitive, so %avs matches lowercase avs but does not match uppercase AVS.

Example: How to Use a Case-Sensitive LIKE Search in MySQL

Suppose we have a table named athletes containing information about basketball players.

We can create the table with:

CREATETABLE athletes (    id INTPRIMARYKEY,    team TEXT NOTNULL,    position TEXT NOTNULL,    points INTNOTNULL);

Next, insert some sample data:

INSERTINTO athletes VALUES(1, 'Mavs', 'Guard', 15),(2, 'mavs', 'Guard', 22),(3, 'MAVS', 'Forward', 36),(4, 'Spurs', 'Guard', 18),(5, 'spurs', 'Forward', 40),(6, 'CAVS', 'Forward', 25);

We can view the data using:

SELECT*FROM athletes;

The output is:

+----+-------+----------+--------+| id | team  | position | points |+----+-------+----------+--------+|  1 | Mavs  | Guard    |     15 ||  2 | mavs  | Guard    |     22 ||  3 | MAVS  | Forward  |     36 ||  4 | Spurs | Guard    |     18 ||  5 | spurs | Forward  |     40 ||  6 | CAVS  | Forward  |     25 |+----+-------+----------+--------+

Notice that the team column contains different capitalization styles:

• Mavs
• mavs
• MAVS
• Spurs
• spurs
• CAVS

Suppose we want to find teams whose names end with avs, specifically with the letters a, v, and s in lowercase.

Using LIKE for a Case-Insensitive Search

First, consider the standard LIKE operator:

SELECT*FROM athletesWHERE team LIKE'%avs';

The % wildcard means that any number of characters can appear before avs.

Depending on the column’s collation, a case-insensitive comparison can match both uppercase and lowercase versions.

For the sample data, the result can be:

+----+------+----------+--------+| id | team | position | points |+----+------+----------+--------+|  1 | Mavs | Guard    |     15 ||  2 | mavs | Guard    |     22 ||  3 | MAVS | Forward  |     36 ||  6 | CAVS | Forward  |     25 |+----+------+----------+--------+

This happens because the comparison treats avs, AVS, and mixed-case variations as equivalent under a case-insensitive collation.

Use LIKE BINARY for a Case-Sensitive Search

If you need the pattern to match exactly with respect to character case, you can use:

SELECT*FROM athletesWHERE team LIKEBINARY'%avs';

The result is:

+----+------+----------+--------+| id | team | position | points |+----+------+----------+--------+|  1 | Mavs | Guard    |     15 ||  2 | mavs | Guard    |     22 |+----+------+----------+--------+

The important difference is that the pattern:

%avs

requires lowercase avs.

Therefore:

Mavs  → matchesmavs  → matchesMAVS  → does not matchCAVS  → does not match

The Mavs value matches because the first character is not part of the case-sensitive avs portion.

How the % Wildcard Works

The % character is a wildcard in a MySQL LIKE expression.

For example:

WHERE team LIKE'%avs'

means:

Find values that end with avs.

The pattern can match:

MavsmavsCavsCAVS

when the comparison is case-insensitive.

The % can also be placed in different positions.

Find Values That Start With a Pattern

SELECT*FROM athletesWHERE team LIKE'M%';

This searches for values beginning with M under the applicable collation rules.

Find Values That End With a Pattern

SELECT*FROM athletesWHERE team LIKE'%s';

This searches for values ending in s.

Find Values That Contain a Pattern

SELECT*FROM athletesWHERE team LIKE'%av%';

This searches for values containing av anywhere in the string.

Case-Sensitive Search for Values Starting With M

You can combine BINARY with another pattern:

SELECT*FROM athletesWHERE team LIKEBINARY'M%';

This requires the first character to be uppercase M.

Using BINARY with Other LIKE Patterns

LIKE BINARY isn’t limited to suffix searches.

For example, to find values beginning with lowercase m:

SELECT*FROM athletesWHERE team LIKEBINARY'm%';

To find values containing lowercase avs anywhere:

SELECT*FROM athletesWHERE team LIKEBINARY'%avs%';

To find a value that exactly matches lowercase mavs:

SELECT*FROM athletesWHERE team LIKEBINARY'mavs';

In this last example, the value mavs matches, while Mavs and MAVS do not.

LIKE BINARY vs Regular LIKE

The main difference can be summarized as follows:

The important point is that plain LIKE is not universally case-insensitive in MySQL. Its behavior depends on the collation being used for the comparison.

Why Collation Matters

MySQL string comparisons are affected by character sets and collations.

For example, a case-insensitive collation may treat:

aA

as equivalent.

A case-sensitive collation distinguishes between them.

Therefore, the behavior of:

SELECT*FROM athletesWHERE team LIKE'%avs';

can depend on the collation of team.

You can inspect a table’s definition with:

SHOW CREATETABLE athletes;

You can also inspect the collation of a specific column with:

SHOW FULL COLUMNSFROM athletes;

This is useful when debugging unexpected LIKE results.

Another Way to Force Case-Sensitive Matching

Instead of BINARY, you can explicitly use a case-sensitive or binary collation.

For example, with a suitable collation:

SELECT*FROM athletesWHERE team COLLATE utf8mb4_bin LIKE'%avs';

This is particularly useful when you want to make the comparison behavior explicit while continuing to work with a character string rather than converting the expression to binary data.

The exact collation available should match the character set of the column.

BINARY Operator vs BINARY Data Type

There is an important distinction between using BINARY as an operator and the BINARY data type.

In:

WHERE team LIKEBINARY'%avs'

BINARY causes the pattern to be treated as a binary string, making the comparison case-sensitive.

You can also write:

WHEREBINARY team LIKE'%avs'

This converts the team expression to a binary string for the comparison.

For simple case-sensitive searches, both approaches can be useful:

SELECT*FROM athletesWHERE team LIKEBINARY'%avs';

or:

SELECT*FROM athletesWHEREBINARY team LIKE'%avs';

Case-Sensitive Exact Matching

If you don’t need pattern matching, = can be used with a binary comparison.

For example:

SELECT*FROM athletesWHEREBINARY team ='mavs';

This returns only:

mavs

and not:

MavsMAVS

This is useful when you need an exact case-sensitive comparison rather than a wildcard search.

Case-Sensitive Search Using LOWER() or UPPER()

Another technique is to normalize both the column and search term.

For example:

SELECT*FROM athletesWHERE LOWER(team) LIKE'%avs';

However, this deliberately converts the column to lowercase, so it does not preserve case sensitivity.

If your requirement is specifically:

Match only lowercase avs.

then BINARY or an appropriate case-sensitive collation is a better choice.

Using a Case-Sensitive Search in Data Cleaning

Case-sensitive searches are useful when cleaning inconsistent data.

Suppose a database contains:

MavsmavsMAVSCAVS

You could identify records containing lowercase avs with:

SELECT*FROM athletesWHERE team LIKEBINARY'%avs';

You could then update or standardize those records if appropriate.

For example:

UPDATE athletesSET team ='Mavs'WHERE team LIKEBINARY'mavs';

Before making such changes to production data, it is best to preview the affected rows:

SELECT*FROM athletesWHERE team LIKEBINARY'mavs';

Case-Sensitive Search for User Input

Case-sensitive LIKE searches can also be useful in applications where capitalization has meaning.

For example, suppose a system stores codes such as:

abc123ABC123Abc123

A case-sensitive search can distinguish between these values:

SELECT*FROM product_codesWHERE code LIKEBINARY'ABC123';

This is important for identifiers, API keys, case-sensitive codes, and other data where uppercase and lowercase characters represent different values.

Performance Considerations

When working with large tables, LIKE pattern design can affect query performance.

A pattern such as:

WHERE team LIKE'M%'

starts with a fixed prefix and may be able to use an appropriate index.

A pattern such as:

WHERE team LIKE'%avs'

starts with %, meaning MySQL generally cannot use a normal B-tree index to efficiently locate rows based on that leading pattern.

This becomes important when searching millions of records.

If case-sensitive searches are frequent in a large production database, consider the column’s character set, collation, indexes, and query pattern when designing the schema.

Complete MySQL Example

Here is a complete example you can copy and run:

CREATETABLE athletes (    id INTPRIMARYKEY,    team VARCHAR(50) NOTNULL,    position VARCHAR(50) NOTNULL,    points INTNOTNULL);INSERTINTO athletes VALUES(1, 'Mavs', 'Guard', 15),(2, 'mavs', 'Guard', 22),(3, 'MAVS', 'Forward', 36),(4, 'Spurs', 'Guard', 18),(5, 'spurs', 'Forward', 40),(6, 'CAVS', 'Forward', 25);-- Case-insensitive LIKE searchSELECT*FROM athletesWHERE team LIKE'%avs';-- Case-sensitive LIKE searchSELECT*FROM athletesWHERE team LIKEBINARY'%avs';

The first query may return:

MavsmavsMAVSCAVS

under a case-insensitive collation.

The second query returns only:

Mavsmavs

because the avs portion of the pattern must match with the same character case.

Frequently Asked Questions

Is LIKE case-sensitive in MySQL?

Not always. LIKE follows the comparison rules of the relevant character set and collation. With commonly used case-insensitive collations, it is case-insensitive.

How do I make LIKE case-sensitive in MySQL?

A simple approach is:

SELECT*FROM athletesWHERE team LIKEBINARY'%avs';

You can also use an appropriate case-sensitive collation.

What does LIKE BINARY do?

LIKE BINARY forces the pattern comparison to be binary and therefore case-sensitive.

How do I search for only lowercase values?

For example:

SELECT*FROM athletesWHERE team LIKEBINARY'%avs';

This distinguishes lowercase avs from uppercase AVS.

How do I perform an exact case-sensitive search?

Use:

SELECT*FROM athletesWHEREBINARY team ='mavs';

Does BINARY affect the entire LIKE comparison?

Yes. You can apply it to the pattern:

team LIKEBINARY'%avs'

or to the column:

BINARY team LIKE'%avs'

Both force a binary comparison.

Conclusion

MySQL’s LIKE operator is extremely useful for pattern matching, but its case sensitivity depends on the collation used for the comparison. If you need to explicitly perform a case-sensitive search, one straightforward option is to use BINARY.

For example:

SELECT*FROM athletesWHERE team LIKEBINARY'%avs';

With sample values such as:

MavsmavsMAVSCAVS

the query matches:

Mavsmavs

but not:

MAVSCAVS

For simple case-sensitive pattern matching, LIKE BINARY is convenient. For larger applications and production databases, however, it is also important to understand MySQL collations, character sets, and indexing, because they determine both the behavior and potential performance of string searches.