Database Reference
In-Depth Information
SELECT common_name AS 'Hawks'
FROM birds
WHERE common_name REGEXP BINARY 'Hawk'
AND common_name NOT REGEXP 'Hawk-Owl'
ORDER BY family_id LIMIT 10;
+-------------------+
| Hawks |
+-------------------+
| Red-tailed Hawk |
| Bicolored Hawk |
| Common Black-Hawk |
| Cuban Black-Hawk |
| Rufous Crab Hawk |
| Great Black-Hawk |
| Black-faced Hawk |
| White-browed Hawk |
| Ridgway's Hawk |
| Broad-winged Hawk |
+-------------------+
I stated that
REGEXP
and
NOT REGEXP
arecase insensitive, unless you add the
BINARY
option as we did to stipulate the collating method as binary (e.g., the letter
H
has a differ-
ent binary value fromn the letter
h
). For the
common_name
column, though, we didn't
need to add the
BINARY
option because the column has a binary collation setting. We did
this unknowingly when we created the
rookery
database near the beginning of
SHOW CREATE DATABASE rookery \G
*************************** 1. row ***************************
Database: rookery
Create Database: CREATE DATABASE `rookery` /*!40100 DEFAULT
CHARACTER SET latin1 COLLATE latin1_bin */
The
COLLATE
clause is setto
latin1_bin
, meaning Latin1binary. Any columns that
we create in tables in the
rookery
database, unless we specify otherwise, will be col-
lated using
latin1_bin
. Execute the following statement to see how the
com-
mon_name
column in the
birds
table is set:
SHOW FULL COLUMNS
FROM birds LIKE 'common_name' \G
*************************** 1. row ***************************
Field: common_name