Working with Strings - MySQL - page 156

and A are unequal.) To compare binary strings such that lettercase does not matter,
convert them to nonbinary strings that have a case-insensitive collation.
• A nonbinary string is a sequence of characters and is compared in character units.
(Depending on the character set, some characters might have multiple bytes.) The
string has a character set that defines the legal characters and a collation that defines
their sort order. The collation also determines whether to consider characters in
different lettercases the same in comparisons. If the collation is case sensitive, and
you want a case-insensitive collation (or vice versa), convert the strings to use a
collation with the desired case-comparison properties.
By default, strings have a character set of latin1 and a collation of latin1_swed
ish_ci unless you reconfigure the server (see Recipe 22.1 ). This results in case-
insensitive string comparisons.
The following example shows how two binary strings that compare as unequal can be
handled so that they are equal when compared as case-insensitive nonbinary strings:
mysql> SET @s1 = BINARY 'cat', @s2 = BINARY 'CAT';
mysql> SELECT @s1 = @s2;
+-----------+
| @s1 = @s2 |
+-----------+
| 0 |
+-----------+
mysql> SET @s1 = CONVERT(@s1 USING latin1) COLLATE latin1_swedish_ci;
mysql> SET @s2 = CONVERT(@s2 USING latin1) COLLATE latin1_swedish_ci;
mysql> SELECT @s1 = @s2;
+-----------+
| @s1 = @s2 |
+-----------+
| 1 |
+-----------+
In this case, because latin1_swedish_ci is the default collation for latin1 , you can
omit the COLLATE operator:
mysql> SET @s1 = CONVERT(@s1 USING latin1);
mysql> SET @s2 = CONVERT(@s2 USING latin1);
mysql> SELECT @s1 = @s2;
+-----------+
| @s1 = @s2 |
+-----------+
| 1 |
+-----------+
The next example shows how to compare, in case-sensitive fashion, two strings that are
not case sensitive:
mysql> SET @s1 = _latin1 'cat', @s2 = _latin1 'CAT';
mysql> SELECT @s1 = @s2;