MariaDB STRCMP() Function

In MariaDB, STRCMP() is a built-in string function that compares two strings and returns 0, -1 or 1 as the result of the comparison.

The MariaDB STRCMP() function compares two strings based on the character collation that the strings adopt.

MariaDB `STRCMP()` Syntax

Here is the syntax of the MariaDB STRCMP() function:

STRCMP(str1, str2)

Parameters

str1: Required. The first string to compare.
str2: Required. The 2nd string to be compared.

If you provide the wrong number of parameters, MariaDB will report an error: ERROR 1582 (42000): Incorrect parameter count in the call to native function 'STRCMP'.

Return value

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

If str1 equals str1, the STRCMP() function will return 0.
If str1 is less than str1, the STRCMP() function will return -1.
If str1 is greater than str1, the STRCMP() function will return 1.

If any number of arguments are NULL, the STRCMP() function will return NULL.

MariaDB `STRCMP()` Examples

Basic usage

This statement shows the basic usage of the MariaDB STRCMP() function:

SELECT
    STRCMP('abc', 'abc'),
    STRCMP('abc', 'def'),
    STRCMP('def', 'abc')\G

Output:

STRCMP('abc', 'abc'): 0
STRCMP('abc', 'def'): -1
STRCMP('def', 'abc'): 1

Collation

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

SET @s1 = _latin7 'x' COLLATE latin7_general_ci;
SET @s2 = _latin7 'X' COLLATE latin7_general_ci;
SET @s3 = _latin7 'x' COLLATE latin7_general_cs;
SET @s4 = _latin7 'X' COLLATE latin7_general_cs;
SELECT STRCMP(@s1, @s2), STRCMP(@s3, @s4);

Output:

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

here:

The collation (COLLATE) of @s1 and @s2 is latin7_general_ci and it is case-insensitive, so the comparison x of X and is returned 0.
The collation (COLLATE) of @s3 and @s4 is latin7_general_cs and it is case-sensitive, so the comparison x of X and is returned -1.

Conclusion

The MariaDB STRCMP() function is used to compare two strings and return the result of the comparison.

MariaDB STRCMP() Syntax