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 | ...
Get MySQL Cookbook 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.