Case Sensitivity in String Comparisons

String comparisons are somewhat more complex than numeric or temporal comparisons. A letter in uppercase may compare as the same or different than the same letter in lowercase, and a letter with one type of accent may be considered the same or different than that letter with another type of accent.• Character Sets and Collations - String expressions contain characters from a particular character set, which is associated with one of the collations (sorting orders) available for the character set.Characters may consist of single or multiple bytes. A collation can be case insensitive (lettercase is not significant) or case sensitive (lettercase is significant).The rules that govern string comparison apply in several ways. They determine the result of comparisons performed explicitly with operators such as = and <, and comparisons performed implicitly by ORDER BY,GROUP BY, and DISTINCT operations.The default character set and collation for literal strings depend on the values of the character_set_connection and collation_connection system variables. The default character set is latin1. The default collation is latin1_swedish_ci, which is case insensitive as indicated by the “_ci” at the end of the collation name. Assuming these connection settings, literal strings

are not case sensitive by default. The comparing strings that differ only in lettercase can be seen in the following example:

mysql> SELECT 'Hello' = 'hello';

+-------------------+

| 'Hello' = 'hello' |

+-------------------+

| 1                      |

+-------------------+

A given collation might cause certain accented characters to compare the same as other characters. For example, 'ü' and 'ue' are different in the default latin1_swedish_ci collation, but with the latin1_german2_ci collation (“German phone-book” collation), they have the same sort value and thus compare as equal:

mysql> SELECT 'Müller' = 'Mueller';

+-----------------------+

| 'Müller' = 'Mueller' |

+------------------------+

| 0                             |

+------------------------+

mysql> SET collation_connection = latin1_german2_ci;

mysql> SELECT 'Müller' = 'Mueller';

+------------------------+

| 'Müller' = 'Mueller' |

+------------------------+

| 1                             |

+------------------------+