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 is used to compare two strings. The
STRCMP() function is usually used with the
STRCMP() is not case sensitive.
Syntax for MySQL STRCMP()
Where string1 and string2 are the two strings that are to be compared.
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.
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;
And the output we get is,
- 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;
And we get the output as,
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;
And the output we get is,
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;
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() With Tables
Consider the below 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;
And we get our output as,
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;
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() 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.
- MySQL Official Documentation on