MySQL | STRCMP method
Start your free 7-days trial now!
MySQL's STRCMP(~) method compares two strings and returns one of 0, -1, 1 depending on the result of the comparison.
One string is considered greater than another if its weight string is greater than the weight string of the other string. To learn more about weight strings visit here.
Parameters
1. expr1 | string
The string to be compared with expr2.
2. expr2 | string
The string to be compared with expr1.
Return value
Case | Return value |
|---|---|
expr1 = expr2 | 0 |
expr1 < expr2 | -1 |
expr1 > expr2 | 1 |
Examples
Basic usage
To check whether weight string of 'example' is greater than that of 'example1':
SELECT STRCMP('example', 'example1');
+-------------------------------+| STRCMP('example', 'example1') |+-------------------------------+| -1 |+-------------------------------+
As the weight string of 'example' is less than that of 'example1' -1 is returned.
To check whether weight string of 'example' is greater than that of 'Example':
SELECT STRCMP('example', 'Example');
+------------------------------+| STRCMP('example', 'Example') |+------------------------------+| 0 |+------------------------------+
The two strings are considered equal as the collation is set to utf8mb4_0900_ai_ci (i.e. case insensitive). You can refer here for how to check the collation of a string.
To check whether weight string of 'example1' is greater than that of 'example':
SELECT STRCMP('example1', 'example');
+-------------------------------+| STRCMP('example1', 'example') |+-------------------------------+| 1 |+-------------------------------+
As the weight string of 'example1' is greater than that of 'example' 1 is returned.
Specifying collation
If we assign a case sensitive collation to the strings to be compared, the comparison becomes case sensitive:
SET @s1 = _utf8mb4 'example' COLLATE utf8mb4_0900_as_cs;SET @s2 = _utf8mb4 'Example' COLLATE utf8mb4_0900_as_cs;SELECT STRCMP(@s1, @s2);
+------------------+| STRCMP(@s1, @s2) |+------------------+| -1 |+------------------+
Now we can see that the weight string of 'Example' is greater than that of 'example'.
If the collations are incompatible an error is thrown:
SET @s1 = _utf8mb4 'example' COLLATE utf8mb4_0900_as_cs;SET @s2 = _utf8mb4 'Example' COLLATE utf8mb4_0900_as_ci;SELECT STRCMP(@s1, @s2);
ERROR 1267 (HY000): Illegal mix of collations (utf8mb4_0900_as_cs,IMPLICIT) and (utf8mb4_0900_as_ci,IMPLICIT) for operation 'strcmp'
As the two collations utf8mb4_0900_as_cs (case sensitive) and utf8mb4_0900_as_ci (case insensitive) are not compatible, comparison is not possible between the two strings. The collation of one of the strings must be matched to align with the other.
