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:

0	The strings are the same.
-1	The first argument is smaller than the second according to the current sort order.
1	The 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'