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 comparison characteristics of the strings.
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 also apply when you use a string column as the
argument to the MIN()
or MAX()
functions because they are based
on comparison. To alter how these functions work with a string column,
you must alter the column’s comparison properties. Controlling Case Sensitivity in String Comparisons discusses how to control
these properties, and Controlling Case Sensitivity of String Sorts shows
how they apply to string sorts. The same principles apply to finding
minimum and maximum string values, so I’ll just summarize here, and
you can read Controlling Case Sensitivity of String Sorts for additional
details.
To compare case-insensitive strings in case-sensitive fashion, order the values using a case-sensitive collation:
SELECT MIN(str_col COLLATE latin1_general_cs) AS min, MAX(str_col COLLATE latin1_general_cs) AS max FROM tbl; ...
Get MySQL Cookbook, 2nd Edition now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.