How to Use STRCMP() to Compare 2 Strings in MySQL

One of the string functions in MySQL is STRCMP(). This function allows you to compare two strings according to their sort order.

The function accepts two arguments. Each argument is the string to compare. It returns either 1, -1, or 0, depending on whether the first string is larger, smaller, or the same size as the second string, according to sort order.

Syntax

Here’s the syntax:

STRCMP(expr1,expr2)

Where expr1 is the first string and expr2 is the second string.

Examples

Here’s an example where the first string is smaller than the second string according to sort order:

SELECT STRCMP('A', 'B');

Result:

+------------------+
| STRCMP('A', 'B') |
+------------------+
|               -1 |
+------------------+

If we swap the arguments around we get this:

SELECT STRCMP('B', 'A');

Result:

+------------------+
| STRCMP('B', 'A') |
+------------------+
|                1 |
+------------------+

And if we use the same string for both arguments we get this:

SELECT STRCMP('A', 'A');

Result:

+------------------+
| STRCMP('A', 'A') |
+------------------+
|                0 |
+------------------+

Of course, the strings can (and probably will) consist of more than a single character:

SELECT STRCMP('A big box', 'Wind and rain') AS Result;

Result:

+--------+
| Result |
+--------+
|     -1 |
+--------+

Collation

The STRCMP() function uses the collation when comparing the strings. This means that you could get different results depending on the collation being used. The following examples demonstrate this.

Case Insensitive

In this example we perform the comparison using a case insensitive collation (the _ci  part of the collation means case insensitive):

SET @lowercase = _utf8mb4 'a' COLLATE utf8mb4_0900_ai_ci;
SET @uppercase = _utf8mb4 'A' COLLATE utf8mb4_0900_ai_ci;

SELECT STRCMP(@lowercase, @uppercase) AS Result;

Result:

+--------+
| Result |
+--------+
|      0 |
+--------+

Case Sensitive

In this example we perform the comparison using a case sensitive collation (the _cs  part of the collation means case sensitive):

SET @lowercase = _utf8mb4 'a' COLLATE utf8mb4_0900_as_cs;
SET @uppercase = _utf8mb4 'A' COLLATE utf8mb4_0900_as_cs;

SELECT STRCMP(@lowercase, @uppercase) AS Result;

Result:

+--------+
| Result |
+--------+
|     -1 |
+--------+

In MySQL, collation can be set at various levels (e.g. connection level, database level, column level, etc) . If you’re not sure what collation is being used, see How to Find the Collation in MySQL.