MySQL Cookbook, 2nd Edition

Controlling Case Sensitivity in Pattern Matching

Problem

A pattern match is case-sensitive when you don’t want it to be, or vice versa.

Solution

Alter the case sensitivity of the strings.

Discussion

The case sensitivity of a pattern match operation is like that of a string comparison. That is, it depends on whether the operands are binary or nonbinary strings, and for nonbinary strings, it depends on their collation. See Controlling Case Sensitivity in String Comparisons for discussion of how these factors apply to comparisons.

The default character set and collation are latin1 and latin1_swedish_ci, so pattern match operations are not case-sensitive by default:

mysql>SELECT 'a' LIKE 'A', 'a' REGEXP 'A';
+--------------+----------------+
| 'a' LIKE 'A' | 'a' REGEXP 'A' |
+--------------+----------------+
|            1 |              1 |
+--------------+----------------+

Note that a REGEXP operation that is not case-sensitive can lead to some unintuitive results:

mysql>SELECT 'a' REGEXP '[[:lower:]]', 'a' REGEXP '[[:upper:]]';
+--------------------------+--------------------------+
| 'a' REGEXP '[[:lower:]]' | 'a' REGEXP '[[:upper:]]' |
+--------------------------+--------------------------+
|                        1 |                        1 |
+--------------------------+--------------------------+

Both expressions are true because [:lower:] and [:upper:] are equivalent when case sensitivity doesn’t matter.

If a pattern match uses different case-sensitive behavior from what you want, control it the same way as for string comparisons: convert the strings ...