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.