STRCMP( )

This MySQL function compares two strings to determine whether the first string is before or after the second string in ASCII sequence.

Syntax

Below is the syntax for this command. Text within square brackets (i.e, [ and ] ) are optional. Choices are separated by bars (i.e, | ). Ellipses preceded by a comma indicates a repeating pattern. Ellipses before or after syntax are used to highlight a relevant except from the larger syntax. Text displayed in italic letters represent text that should be replaced with the specific names related to the database (e.g., column with the name of the column).

STRCMP(string, string)

Explanation

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, -1 is returned. If it follows the second, 1 is returned. If they are equal, 0 is returned. This function is often used for alphanumeric comparisons, but is case insensitive unless at least one of the strings given are binary.

Examples

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're using 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 only see the results where the courses are out of sequence, we're using a subquery with a WHERE clause to return only rows for which the STRCMP( ) returns a -1 value, indicate 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, this doesn't resolve the problem either. It only provides 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.