MySQL Cookbook, 2nd Edition

Controlling Case Sensitivity of String Sorts

Problem

String sorting operations are case-sensitive when you don’t want them to be, or vice versa.

Solution

Alter the comparison characteristics of the sorted values.

Discussion

Chapter 5 discusses how string comparison properties depend on whether the strings are binary or nonbinary:

• Binary strings are sequences of bytes. They are compared byte by byte using numeric byte values. Character set and lettercase have no meaning for comparisons.

• Nonbinary strings are sequences of characters. They have a character set and collation and are compared character by character using the order defined by the collation.

These properties apply to string sorting as well, because sorting is based on comparison. To alter the sorting properties of a string column, you must alter its comparison properties. (For a summary of which string data types are binary and nonbinary, see Choosing a String Data Type.)

The examples in this section use a table that has case-insensitive and case-sensitive nonbinary columns, and a binary column:

CREATE TABLE str_val
(
  ci_str   CHAR(3) CHARACTER SET latin1 COLLATE latin1_swedish_ci,
  cs_str   CHAR(3) CHARACTER SET latin1 COLLATE latin1_general_cs,
  bin_str  BINARY(3)
);

Suppose that the table has the following contents:

+--------+--------+---------+
| ci_str | cs_str | bin_str |
+--------+--------+---------+
| AAA    | AAA    | AAA     |
| aaa    | aaa    | aaa     |
| bbb    | bbb    | bbb     |
| BBB    | BBB    | BBB     |
+--------+--------+---------+

Each column contains ...