Controlling Case Sensitivity in Pattern Matching

4.11.1 Problem

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

4.11.2 Solution

Alter the case sensitivity of the strings.

4.11.3 Discussion

By default, LIKE is not case sensitive:

mysql> SELECT name, name LIKE '%i%', name LIKE '%I%' FROM metal; +----------+-----------------+-----------------+ | name | name LIKE '%i%' | name LIKE '%I%' | +----------+-----------------+-----------------+ | copper | 0 | 0 | | gold | 0 | 0 | | iron | 1 | 1 | | lead | 0 | 0 | | mercury | 0 | 0 | | platinum | 1 | 1 | | silver | 1 | 1 | | tin | 1 | 1 | +----------+-----------------+-----------------+

Currently, REGEXP is not case sensitive, either.

mysql> SELECT name, name REGEXP 'i', name REGEXP 'I' FROM metal; +----------+-----------------+-----------------+ | name | name REGEXP 'i' | name REGEXP 'I' | +----------+-----------------+-----------------+ | copper | 0 | 0 | | gold | 0 | 0 | | iron | 1 | 1 | | lead | 0 | 0 | | mercury | 0 | 0 | | platinum | 1 | 1 | | silver | 1 | 1 | | tin | 1 | 1 | +----------+-----------------+-----------------+

However, prior to MySQL 3.23.4, REGEXP operations are case sensitive:

mysql> SELECT name, name REGEXP 'i', name REGEXP 'I' FROM metal; +----------+-----------------+-----------------+ | name | name REGEXP 'i' | name REGEXP 'I' | +----------+-----------------+-----------------+ | copper | 0 | 0 | | gold | 0 | 0 | | iron | 1 | 0 | | lead | 0 | 0 | | mercury | 0 | 0 | | platinum | 1 | 0 | | silver | 1 | 0 | | tin | 1 | 0 | +----------+-----------------+-----------------+

Note that the (current) behavior of REGEXP not being 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 than what you want, control it the same way as for string comparisons:

Категории