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