Database Reference
In-Depth Information
| space alien | Space alien |
+--------------+--------------------+
For more information about writing stored functions, see
Chapter 9
.
5.7. Controlling Case Sensitivity in String Comparisons
Problem
You want to know whether strings are equal or unequal, or which 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. 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';
+---------------+---------------+----------------+----------------+
| 'cat' = 'cat' | 'cat' = 'dog' | 'cat' <> 'cat' | 'cat' <> 'dog' |
+---------------+---------------+----------------+----------------+
| 1 | 0 | 0 | 1 |
+---------------+---------------+----------------+----------------+
mysql>
SELECT 'cat' < 'awk', 'cat' < 'dog', 'cat' BETWEEN 'awk' AND 'eel';
+---------------+---------------+-------------------------------+
| 'cat' < 'awk' | 'cat' < 'dog' | 'cat' BETWEEN 'awk' AND 'eel' |
+---------------+---------------+-------------------------------+
| 0 | 1 | 1 |
+---------------+---------------+-------------------------------+
However, comparison and sorting properties of strings are subject to complications that
don't apply to other types of data. For example, sometimes you must ensure that a string
comparison is case sensitive that would not otherwise be, or vice versa. This section
describes how to do that.
String comparison properties depend on whether the operands are binary or nonbinary
strings:
• A binary string is a sequence of bytes and is compared using numeric byte values.
Lettercase has no meaning. However, because letters in different cases have different
byte values, comparisons of binary strings effectively are case sensitive. (That is,
a
