MySQL Cookbook, 2nd Edition

Controlling Case Sensitivity in String Comparisons

Problem

You want to know whether strings are equal or unequal, or which one appears first in lexical order.

Solution

Use a comparison operator. But remember that strings have properties such as case sensitivity that you must take into account. For example, a string comparison might be case-sensitive when you don’t want it to be, or vice versa.

Discussion

As for other data types, you can compare string values for equality, inequality, or relative ordering:

mysql>SELECT 'cat' = 'cat', 'cat' = 'dog';
+---------------+---------------+
| 'cat' = 'cat' | 'cat' = 'dog' |
+---------------+---------------+
|             1 |             0 |
+---------------+---------------+
mysql> SELECT 'cat' != 'cat', 'cat' != 'dog';
+----------------+----------------+
| 'cat' != 'cat' | 'cat' != 'dog' |
+----------------+----------------+
|              0 |              1 |
+----------------+----------------+
mysql> SELECT 'cat' < 'awk', 'cat' < 'dog';
+---------------+---------------+
| 'cat' < 'awk' | 'cat' < 'dog' |
+---------------+---------------+
|             0 |             1 |
+---------------+---------------+
mysql> SELECT 'cat' BETWEEN 'awk' AND 'egret';
+---------------------------------+
| 'cat' BETWEEN 'awk' AND 'egret' |
+---------------------------------+
|                               1 |
+---------------------------------+

However, comparison and sorting properties of strings are subject to certain complications that don’t apply to other types of data. For example, sometimes you need to make sure a string operation is case-sensitive that would ...