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.