MySQL in a Nutshell, 2nd Edition

Name

STRCMP()

Synopsis

STRCMP(string, string)

This function compares two strings to determine whether the first string is before or after the second string in ASCII sequence. If the first string precedes the second string, –1 is returned. If the first follows the second, 1 is returned. If they are equal, 0 is returned. This function is often used for alphanumeric comparisons, but it is case-insensitive unless at least one of the strings given is binary. Here is an example:

SELECT * FROM
(SELECT STRCMP(
   SUBSTR(pre_req, 1, 8),
   SUBSTR(pre_req, 10, 8))
AS Comparison
FROM courses) AS derived1
WHERE Comparison = 1;

In this example, because course codes are all eight characters long, we use SUBSTR() to extract the first two course code numbers. Using STRCMP(), we compare the two course codes to see if they’re in sequence. To see only the results where the courses are out of sequence, we use a subquery with a WHERE clause to return only rows for which the STRCMP() returns a –1 value, indicating the two strings are not in sequence.

The problem with this statement is that some courses have more than two prerequisites. We would have to expand this statement to encompass them. However, that doesn’t resolve the problem either; it provides only more indications of what we know. To reorder the data, it would be easier to create a simple script using one of the APIs to extract, reorder, and then replace the column values.