A pattern match against a binary column is case sensitive.

       
mysql>
mysql> CREATE TABLE mytable
    -> (
    ->  name    VARCHAR(20)
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO mytable (name)
    ->  VALUES
    ->          ('copper'),
    ->          ('gold'),
    ->          ('iron'),
    ->          ('lead'),
    ->          ('mercury'),
    ->          ('platinum'),
    ->          ('silver'),
    ->          ('tin')
    -> ;
Query OK, 8 rows affected (0.00 sec)
Records: 8  Duplicates: 0  Warnings: 0

mysql>
mysql> SELECT * FROM mytable;
+----------+
| name     |
+----------+
| copper   |
| gold     |
| iron     |
| lead     |
| mercury  |
| platinum |
| silver   |
| tin      |
+----------+
8 rows in set (0.00 sec)

mysql>
mysql> ALTER TABLE mytable ADD binname VARCHAR(20) BINARY;
Query OK, 8 rows affected (0.01 sec)
Records: 8  Duplicates: 0  Warnings: 0

mysql>
mysql> UPDATE mytable SET binname = name;
Query OK, 8 rows affected (0.00 sec)
Rows matched: 8  Changed: 8  Warnings: 0

mysql>
mysql> SELECT binname, binname LIKE '%I%', binname REGEXP 'I' FROM mytable;
+----------+--------------------+--------------------+
| 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 |
+----------+--------------------+--------------------+
8 rows in set (0.00 sec)

mysql> SELECT binname, UPPER(binname) LIKE '%I%', UPPER(binname) REGEXP 'I' FROM mytable;
+----------+---------------------------+---------------------------+
| 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 |
+----------+---------------------------+---------------------------+
8 rows in set (0.00 sec)

mysql>
mysql> drop table mytable;
Query OK, 0 rows affected (0.00 sec)

mysql>

Related examples in the same category

1.	Using Binary with REGEXP
2.	Force name values to be case sensitive using BINARY
3.	Using BINARY has the effect of causing [:lower:] and [:upper:] in regular expressions to act as you would expe
4.	Convert the output column to binary and sort that
5.	To make tstr case sensitive, use BINARY:
6.	Get the binary representation of the values 6 and 10.
7.	Get the decimal values that belong to the binary representations 1001 and 111.
8.	Binary Character String Comparison
9.	Controlling Case Sensitivity of String Sorts
10.	Mix binary and nonbinary string columns within a single table.

A pattern match against a binary column is case sensitive. : Binary « String « SQL / MySQL

Related examples in the same category