MySQL STRCMP() Function

In MySQL, the STRCMP() function compares two strings and returns the result of the comparison. The function compares two strings based on the collation of the strings.

STRCMP() Syntax

Here is the syntax of MySQL STRCMP() function:

STRCMP(str1, str2)

Parameters

str1

Required. A string to be compared.

str2

Required. Another string to be compared.

Return value

The STRCMP(str1, str2) function returns the result of comparing two strings.

• If str1 equals str2, this function will return 0.
• If str1 is less than str2, this function will return -1.
• If str1 is greater than str2, this function will return 1.
• The function will return NULL if any of the arguments are NULL.

STRCMP() Examples

Here are some examples of MySQL STRCMP() function.

SELECT
    STRCMP('hello', 'hello'),
    STRCMP('hello1', 'hello'),
    STRCMP('hello', 'hello1'),
    STRCMP('hello', 'world'),
    STRCMP(NULL, NULL)\G

 STRCMP('hello', 'hello'): 0
STRCMP('hello1', 'hello'): 1
STRCMP('hello', 'hello1'): -1
 STRCMP('hello', 'world'): -1
       STRCMP(NULL, NULL): NULL

The STRCMP() function compares two strings based on the collation of the strings. Let’s look at the following example:

SET @s1 = _utf8mb4 'x' COLLATE utf8mb4_0900_ai_ci;
SET @s2 = _utf8mb4 'X' COLLATE utf8mb4_0900_ai_ci;
SET @s3 = _utf8mb4 'x' COLLATE utf8mb4_0900_as_cs;
SET @s4 = _utf8mb4 'X' COLLATE utf8mb4_0900_as_cs;
SELECT STRCMP(@s1, @s2), STRCMP(@s3, @s4);

+------------------+------------------+
| STRCMP(@s1, @s2) | STRCMP(@s3, @s4) |
+------------------+------------------+
|                0 |               -1 |
+------------------+------------------+

Here:

• The collation (utf8mb4_0900_ai_ci) of @s1 and @s2 is case-insensitive, so STRCMP(@s1, @s2) returns 0.
• The collation (utf8mb4_0900_as_cs) of @s1 and @s2 is case-insensitive, so STRCMP(@s1, @s2) returns -1.