mysql> describe test;
+-------+------+-------------------+------+-----+---------+-------+
| Field | Type | Collation         | Null | Key | Default | Extra |
+-------+------+-------------------+------+-----+---------+-------+
| name  | text | latin1_swedish_ci |      |     |         |       |
+-------+------+-------------------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> select name from test;
+---------------------------+
| name                      |
+---------------------------+
| microbiology              |
| colony[microbiology]      |
| microbiology*1:09:00::    |
| BIOLOGY:microbiology$NOUN |
| microbiology              |
| microbiology              |
| BIOLOGY                   |
| ZOOLOGY                   |
| colony[microbiology]      |
| biology class             |
+---------------------------+
10 rows in set (0.00 sec)

mysql> select name from test where name like binary '%B%';
+---------------------------+
| name                      |
+---------------------------+
| BIOLOGY:microbiology$NOUN |
| BIOLOGY                   |
+---------------------------+
2 rows in set (0.00 sec)

mysql> select name from test where name like binary '%BIO%';
+---------------------------+
| name                      |
+---------------------------+
| BIOLOGY:microbiology$NOUN |
| BIOLOGY                   |
+---------------------------+
2 rows in set (0.00 sec)

mysql> select name from test where name like binary '%BIOL%';
+---------------------------+
| name                      |
+---------------------------+
| microbiology              |
| colony[microbiology]      |
| microbiology*1:09:00::    |
| BIOLOGY:microbiology$NOUN |
| microbiology              |
| microbiology              |
| BIOLOGY                   |
| colony[microbiology]      |
| biology class             |
+---------------------------+
9 rows in set (0.00 sec)

mysql> select name from test where name like binary '%BIOLOGY%';
+---------------------------+
| name                      |
+---------------------------+
| microbiology              |
| colony[microbiology]      |
| microbiology*1:09:00::    |
| BIOLOGY:microbiology$NOUN |
| microbiology              |
| microbiology              |
| BIOLOGY                   |
| colony[microbiology]      |
| biology class             |
+---------------------------+
9 rows in set (0.00 sec)

mysql> \t