MySQL Cookbook

Controlling String Case Sensitivity for MIN( ) and MAX( )

Problem

MIN( ) and MAX( ) select strings in case sensitive fashion when you don’t want them to, or vice versa.

Solution

Alter the case sensitivity of the strings.

Discussion

When applied to string values, MIN( ) and MAX( ) produce results determined according to lexical sorting rules. One factor in string sorting is case sensitivity, so MIN( ) and MAX( ) are affected by that as well. In Chapter 6, we used a textblob_val table containing two columns of apparently identical values:

mysql> SELECT tstr, bstr FROM textblob_val;
+------+------+
| tstr | bstr |
+------+------+
| aaa  | aaa  |
| AAA  | AAA  |
| bbb  | bbb  |
| BBB  | BBB  |
+------+------+

However, although the values look the same, they don’t behave the same. bstr is a BLOB column and is case sensitive. tstr, a TEXT column, is not. As a result, MIN( ) and MAX( ) will not necessarily produce the same results for the two columns:

mysql> SELECT MIN(tstr), MIN(bstr) FROM textblob_val;
+-----------+-----------+
| MIN(tstr) | MIN(bstr) |
+-----------+-----------+
| aaa       | AAA       |
+-----------+-----------+

To make tstr case sensitive, use BINARY:

mysql> SELECT MIN(BINARY tstr) FROM textblob_val;
+------------------+
| MIN(BINARY tstr) |
+------------------+
| AAA              |
+------------------+

To make bstr not case sensitive, you can convert the values to a given lettercase:

mysql> SELECT MIN(LOWER(bstr)) FROM textblob_val; +------------------+ | MIN(LOWER(bstr)) | +------------------+ | aaa | ...