MySQL STRCMP() – How to Compare Two Strings in MySQL?

By /
STRCMP Function

In this tutorial, we will learn about the MySQL STRCMP() function. You may encounter a situation while working with tables where you might have to compare two string values and find out if they are equal or not. Suppose you have two strings, say “SQL” and “Python”.

What if you want to compare them and find out which string is greater among the two or if at all, are they equal? For this purpose, MySQL provides us with the STRCMP() function.

The STRCMP() function is used to compare two strings. The STRCMP() function is usually used with the SELECT statement.  STRCMP() is not case sensitive.

Syntax for MySQL STRCMP()

STRCMP(string1, string2)Code language: SQL (Structured Query Language) (sql)

Where string1 and string2 are the two strings that are to be compared.

The STRCMP() functions gives the following output for the corresponding result cases:

  • 0, if string1 = string2;
  • 1, if string1 > string2;
  • -1, if string1 < string2.

Examples of MySQL STRCMP()

Let’s take a look at some of the examples of the string compare functions.

Basic Examples

Consider the below queries. We use an alias named ComparisonResult to make our output readable. 

SELECT STRCMP('JournalDev', 'JournalDev') AS ComparisonResult; 
SELECT STRCMP('SQL', 'Python') AS ComparisonResult; 
SELECT STRCMP('Ruby on Rails', 'SQL') AS ComparisonResult; 
SELECT STRCMP('SQL', NULL) AS ComparisonResult;Code language: SQL (Structured Query Language) (sql)

And the output we get is,

Strcmp Basic Example
  • The first query outputs a 0 because both the strings are equal.
  • The second query results in 1 since the string “SQL” is greater than “Python” according to the sort order (by default, ascending)
  • The third query results in -1 since the string “Ruby on Rails” is less than “SQL” according to the sort order
  • It is important to note that if either argument of the STRCMP() function is NULL, then the return value is NULL. 

MySQL STRCMP() Is Not Case Sensitive

STRCMP() is not case sensitive. Let us demonstrate this by an example. Consider the below query,

SELECT STRCMP('SQL', 'sql') AS ComparisonResult;Code language: SQL (Structured Query Language) (sql)

And we get the output as,

Strcmp Case Insensitive

This happens because the CHAR and VARCHAR types are not case sensitive by default. However, if convert one of the above strings to BINARY type, STRCMP() will take case sensitivity into consideration because BINARY strings are case sensitive. We can demonstrate this using the below query,

SELECT STRCMP('SQL', BINARY 'sql') AS ComparisonResult;Code language: SQL (Structured Query Language) (sql)

And the output we get is,

Strcmp Binary Case Sensitive

The output above says that the lowercase string with BINARY type is greater than the uppercase ‘SQL’ string. This is because lowercase characters have a higher ASCII value than uppercase characters. You can read about ASCII values and the MySQL ASCII() function for reference.

MySQL STRCMP() Is Multi-byte Safe

STRCMP() is multi-byte safe. This means that strings with the same value but different character sets are equal. Let us demonstrate this using the below query:

SELECT STRCMP('SQL', CONVERT('SQL' USING utf32)) AS ComparisonResult;Code language: SQL (Structured Query Language) (sql)

Over here, we are comparing string ‘SQL’ which belongs to the latin1 character set (the default character set of MySQL) with another string ‘SQL’ which has been converted to the utf32 character set. Since STRCMP() is multi-byte safe, we get the output as 0.

MySQL STRCMP Multi Byte Safe

MySQL STRCMP() With Tables

Consider the below Employee table.

Employee Table Reverse MySQL STRCMP
Employee Table

1. Comparing columns with STRCMP()

How about comparing the values of the Name column and the Department column? We write our query as,

SELECT Name, Department, STRCMP(Name, Department) AS ComparisonResult FROM Employee;Code language: SQL (Structured Query Language) (sql)

And we get our output as,

MySQL STRCMP Table Example

2. Pairing STRCMP() with WHERE

STRCMP() can also be used in the WHERE clause. Let us take an example. How about getting a list of those names which are greater than their corresponding department names? We do so using the query,

SELECT Name FROM Employee WHERE STRCMP(Name, Department)>0;Code language: SQL (Structured Query Language) (sql)

We use the MySQL STRCMP() function in the WHERE clause and check if the values in the Name column are greater than their corresponding values in the Department column.

The output we get is,

Strcmp Where Example

Conclusion

STRCMP() is an important string function and can be used for a lot of use cases. I encourage you to play around with it and check out the references.

References