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

• To make a pattern match case sensitive, use a binary string for either operand (for example, by using the BINARY keyword). The following query shows how the non-binary column name normally is not case sensitive:

  mysql> SELECT name, name LIKE '%i%%', name REGEXP 'i' FROM metal;
  +----------+------------------+-----------------+
  | name     | name LIKE '%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 |
  +----------+------------------+-----------------+

  And this query shows how to force name values to be case sensitive using BINARY:

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

  Using BINARY also has the effect of causing [:lower:] and [:upper:] in regular expressions to act as you would expect. The second expression in the following query yields a result that really is true only for uppercase letters:

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

• A pattern match against a binary column is case sensitive. To make the match not case sensitive, make both operands the same lettercase. To see how this works, modify the metal table to add a binname column that is like the name column except that it is VARCHAR BINARY rather than VARCHAR:

  mysql> ALTER TABLE metal ADD binname VARCHAR(20) BINARY;
  mysql> UPDATE metal SET binname = name;

  The first of the following queries shows how the binary column binname normally is case sensitive in pattern matches, and the second shows how to force it not to be, using UPPER( ):

  mysql> SELECT binname, binname LIKE '%I%', binname REGEXP 'I'
      -> FROM metal;
  +----------+--------------------+--------------------+
  | binname  | binname LIKE '%I%' | binname REGEXP 'I' |
  +----------+--------------------+--------------------+
  | copper   |                  0 |                  0 |
  | gold     |                  0 |                  0 |
  | iron     |                  0 |                  0 |
  | lead     |                  0 |                  0 |
  | mercury  |                  0 |                  0 |
  | platinum |                  0 |                  0 |
  | silver   |                  0 |                  0 |
  | tin      |                  0 |                  0 |
  +----------+--------------------+--------------------+
  mysql> SELECT binname, UPPER(binname) LIKE '%I%', UPPER(binname) REGEXP 'I'
      -> FROM metal;
  +----------+---------------------------+---------------------------+
  | binname  | UPPER(binname) LIKE '%I%' | UPPER(binname) 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 |
  +----------+---------------------------+---------------------------+