How to Compare two Strings in MySQL

Problem

You have two strings to compare alphabetically in MySQL.

Solution 1

The most straightforward method to compare two strings in MySQL is to use standard comparison operators (<, >, =):

SELECT 'Michael' > 'Mike';

And here is the result:

The result of 0 means 'false'. A result of 'true' would show a 1.

The result is 'true' if we use the < (less than) comparison operator. Let's try it:

SELECT 'Michael' < 'Mike';

As discussed above, the result of 1 here means 'true'.

The string 'Mike' is greater than 'Michael' because of the alphabetical value of its letters: the first letter, M, is the same for both words, as is the second one, i. But the third letter k in 'Mike' has a greater value than c, the third letter in 'Michael'. So, 'Mike' has a greater value overall.

Discussion

In MySQL, this kind of comparison is not case-sensitive. So, SELECT 'MIKE' = 'Mike'; returns 'true'.

There is, however, another method for comparing strings in MySQL.

Solution 2

The STRCMP() function in MySQL compares two strings. It produces one of the following three results:

• 0 if both strings are the same.
• -1 if the first string is alphabetically less than the second string.
• 1 if the first string is alphabetically greater than the second string.

Here is a great way to remember the meaning of the result: this function essentially "subtracts" the second argument from the first. So, if the result is less than 0, the first argument is less than the second and vice versa.

Thus, SELECT STRCMP('Michael', 'mike'); returns -1.

Discussion

This function is also case-insensitive: SELECT STRCMP('MIKE', 'mike'); returns 0.