Recipe 5.12. 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 Section 5.9 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 to binary or nonbinary as necessary or change the collation of nonbinary strings.

To make a pattern match case-sensitive, use a case-sensitive collation for either operand. For example, with the latin1 character set, use a collation of latin1_general_cs:

mysql> SET @s = 'a' COLLATE latin1_general_cs; mysql> SELECT @s LIKE 'A', @s REGEXP 'A'; +-------------+---------------+ | @s LIKE 'A' | @s REGEXP 'A' | +-------------+---------------+ |           0 |             0 | +-------------+---------------+ 

Use of a case-sensitive collation also has the effect of causing [:lower:] and [:upper:] in regular expressions to match only lowercase and uppercase characters, respectively. The second expression in the following statement yields a result that really is true only for uppercase letters:

mysql> SET @s = 'a', @s_cs = 'a' COLLATE latin1_general_cs; mysql> SELECT @s REGEXP '[[:upper:]]', @s_cs REGEXP '[[:upper:]]'; +-------------------------+----------------------------+ | @s REGEXP '[[:upper:]]' | @s_cs REGEXP '[[:upper:]]' | +-------------------------+----------------------------+ |                       1 |                          0 | +-------------------------+----------------------------+