How STRCMP() Works in MariaDB

In MariaDB, STRCMP() is a built-in string function that compares two strings. It returns 0, -1, or 1, depending on whether the strings are the same or not.

Here are the possible results and what they mean:

0The strings are the same.
-1The first argument is smaller than the second according to the current sort order.
1The first argument is larger than the second according to the current sort order

Syntax

The syntax goes like this:

STRCMP(expr1,expr2)

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

Example

Here’s a basic example:

SELECT 
    STRCMP('abc', 'abc') AS "1",
    STRCMP('abc', 'def') AS "2",
    STRCMP('def', 'abc') AS "3";

Result:

+---+----+---+
| 1 | 2  | 3 |
+---+----+---+
| 0 | -1 | 1 |
+---+----+---+

This example demonstrates each possible outcome that STRCMP() can produce.

Here’s another example:

SELECT 
    STRCMP('Cat', 'Cattery') AS "1",
    STRCMP('Dog', 'dog') AS "2",
    STRCMP('Cow 07', 'Cow 007') AS "3";

Result:

+----+---+---+
| 1  | 2 | 3 |
+----+---+---+
| -1 | 0 | 1 |
+----+---+---+

Empty Strings vs Spaces

Comparing an empty string to a space results in 0:

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

Result:

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

Null Arguments

Passing null for any argument (or all arguments) returns null:

SELECT 
    STRCMP(null, 'abc'),
    STRCMP('abc', null),
    STRCMP(null, null);

Result:

+---------------------+---------------------+--------------------+
| STRCMP(null, 'abc') | STRCMP('abc', null) | STRCMP(null, null) |
+---------------------+---------------------+--------------------+
|                NULL |                NULL |               NULL |
+---------------------+---------------------+--------------------+

Missing Argument

Calling STRCMP() with the wrong number of arguments, or without passing any arguments results in an error:

SELECT STRCMP();

Result:

ERROR 1582 (42000): Incorrect parameter count in the call to native function 'STRCMP'