Recipe 8.6. 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. Section 5.9 discusses how to control these properties, and Section 7.4 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 Section 7.4 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;
To compare case-sensitive strings in case-insensitive fashion, order the values using a case-insensitive collation: SELECT MIN(str_col COLLATE latin1_swedish_ci) AS min, MAX(str_col COLLATE latin1_swedish_ci) AS max FROM tbl;
Another possibility is to compare values that have all been converted to the same lettercase, which makes lettercase irrelevant. However, that also changes the retrieved values: SELECT MIN(UPPER(str_col)) AS min, MAX(UPPER(str_col)) AS max FROM tbl;
Binary strings compare using numeric byte values, so there is no concept of lettercase involved. However, because letters in different cases have different byte values, comparisons of binary strings effectively are case-sensitive (that is, a and A are unequal). To compare binary strings using a case-insensitive ordering, convert them to nonbinary strings, and apply an appropriate collation: SELECT MIN(CONVERT(str_col USING latin1) COLLATE latin1_swedish_ci) AS min, MAX(CONVERT(str_col USING latin1) COLLATE latin1_swedish_ci) AS max FROM tbl;
If the default collation is case-insensitive (as is true for latin1), you can omit the COLLATE clause.
|
