4.4 Testing String Equality or Relative Ordering
4.4.1 Problem
You want to know whether strings
are equal or unequal, or which one appears first in lexical order.
4.4.2 Solution
Use a comparison operator.
4.4.3 Discussion
Strings are subject to the usual
equality and inequality comparisons:
mysql> SELECT name, name = 'lead', name != 'lead' FROM metal;
+----------+---------------+----------------+
| name | name = 'lead' | name != 'lead' |
+----------+---------------+----------------+
| copper | 0 | 1 |
| gold | 0 | 1 |
| iron | 0 | 1 |
| lead | 1 | 0 |
| mercury | 0 | 1 |
| platinum | 0 | 1 |
| silver | 0 | 1 |
| tin | 0 | 1 |
+----------+---------------+----------------+
You can also use relational operators such as
<, <=,
>=, and > to test strings
for lexical ordering:
mysql> SELECT name, name < 'lead', name > 'lead' FROM metal;
+----------+---------------+---------------+
| name | name < 'lead' | name > 'lead' |
+----------+---------------+---------------+
| copper | 1 | 0 |
| gold | 1 | 0 |
| iron | 1 | 0 |
| lead | 0 | 0 |
| mercury | 0 | 1 |
| platinum | 0 | 1 |
| silver | 0 | 1 |
| tin | 0 | 1 |
+----------+---------------+---------------+
To find out whether a string lies within a given range of values
(inclusive), you can combine two comparisons:
mysql> SELECT name, 'iron' <= name AND name <= 'platinum' FROM metal;
+----------+---------------------------------------+
| name | 'iron' <= name AND name <= 'platinum' |
+----------+---------------------------------------+
| copper | 0 |
| gold | 0 |
| iron | 1 |
| lead | 1 |
| mercury | 1 |
| platinum | 1 |
| silver | 0 |
| tin | 0 |
+----------+---------------------------------------+
You can also use the BETWEEN operator for
inclusive-range testing. The following query is equivalent to the one
just shown:
SELECT name, name BETWEEN 'iron' AND 'platinum' FROM metal;
4.4.4 See Also
The outcome of a string comparison may be affected by whether or not
the operands are binary strings, as discussed in Recipe 4.10.
|
