How the STRCMP() function works in Mariadb?

The STRCMP() function in MariaDB is used to compare two strings lexically (based on their character values). It returns an integer value indicating the result of the comparison. This function is useful when you need to perform string comparisons in your SQL queries or application logic.

Syntax

The syntax for the MariaDB STRCMP() function is as follows:

STRCMP(str1, str2)

• str1: The first string to be compared. This is a required parameter.
• str2: The second string to be compared. This is also a required parameter.

The function returns an integer value:

• 0 if both strings are exactly the same
• -1 if the first string (str1) is lexically smaller than the second string (str2)
• 1 if the first string (str1) is lexically greater than the second string (str2)

Examples

Example 1: Comparing equal strings

This example demonstrates how STRCMP() behaves when comparing two identical strings.

SELECT STRCMP('hello', 'hello') AS result;

The following is the output:

+--------+
| result |
+--------+
|      0 |
+--------+

Since both strings are exactly the same, the STRCMP() function returns 0.

Example 2: Comparing different strings

This example shows how STRCMP() compares two different strings.

SELECT STRCMP('apple', 'banana') AS result;

The following is the output:

+--------+
| result |
+--------+
|     -1 |
+--------+

In this case, the function returns -1 because the string 'apple' is lexically smaller than the string 'banana'.

Example 3: Comparing strings with different cases

The STRCMP() function is case-sensitive by default.

DROP TABLE IF EXISTS example;
CREATE TABLE example (name VARCHAR(50));
INSERT INTO example (name) VALUES ('Alice'), ('alice');

SELECT name, STRCMP(name, 'alice') AS result
FROM example;

The following is the output:

+-------+--------+
| name  | result |
+-------+--------+
| Alice |      0 |
| alice |      0 |
+-------+--------+

In this example, the comparison between 'Alice' and 'alice' returns 0 because MariaDB uses a case-insensitive collation.

Example 4: Comparing strings with NULL values

If either of the compared strings is NULL, the STRCMP() function returns NULL.

DROP TABLE IF EXISTS example;
CREATE TABLE example (name VARCHAR(50));
INSERT INTO example (name) VALUES ('Alice'), (NULL);

SELECT name, STRCMP(name, 'Alice') AS result
FROM example;

The following is the output:

+-------+--------+
| name  | result |
+-------+--------+
| Alice |      0 |
| NULL  |   NULL |
+-------+--------+

In this example, the comparison between NULL and 'Alice' returns NULL.

Example 5: Using STRCMP() in a WHERE clause

The STRCMP() function can be used in a WHERE clause to filter data based on string comparisons.

DROP TABLE IF EXISTS example;
CREATE TABLE example (name VARCHAR(50));
INSERT INTO example (name) VALUES ('Alice'), ('Bob'), ('Charlie'), ('David'), ('alice');

SELECT name
FROM example
WHERE STRCMP(name, 'Alice') = 0;

The following is the output:

+-------+
| name  |
+-------+
| Alice |
| alice |
+-------+

In this example, the query selects the rows where the name column is equal to 'Alice' (case-insensitive).

Related Functions

The following are some functions related to the MariaDB STRCMP() function:

• MariaDB LIKE operator is used for pattern matching on strings.
• MariaDB REGEXP operator is used for pattern matching on strings using regular expressions.
• MariaDB REPLACE() function is used to replace occurrences of a substring within a string with a new substring.
• MariaDB CONCAT() function is used to concatenate two or more strings.

Conclusion

The STRCMP() function in MariaDB is a powerful tool for performing lexical comparisons between strings. It can be used in various scenarios, such as filtering data based on string conditions, sorting strings, or implementing application logic that requires string comparisons. By understanding the behavior of this function and its return values, you can write more efficient and accurate SQL queries and applications.