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
Chapter4 . See how we created the database by entering this from the mysql client:
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
Search WWH ::




Custom Search