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 ...
Get MySQL Cookbook, 2nd Edition now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.