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.
Get MySQL in a Nutshell, 2nd Edition now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.