MySQL Cookbook

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

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 ...