What is MySQL STRCMP() function and what would be the output of this function?

MySQL STRCMP() function, as the name suggests, is used to compare two strings. We need to provide both the strings as arguments of this function. It is shown in the syntax below −

Syntax

STRCMP(Str1, Str2)

Here, 

• Str1 is first string used for comparison.
• Str2 is second string used for comparison.

Example

mysql> Select STRCMP('MySQL', 'MySQL');
+--------------------------+
| STRCMP('MySQL', 'MySQL') |
+--------------------------+
|                        0 |
+--------------------------+
1 row in set (0.00 sec)

mysql> Select STRCMP('MSQL', 'MySQL');
+-------------------------+
| STRCMP('MSQL', 'MySQL') |
+-------------------------+
|                      -1 |
+-------------------------+
1 row in set (0.00 sec)

mysql> Select STRCMP('MySQL', 'MSQL');
+-------------------------+
| STRCMP('MySQL', 'MSQL') |
+-------------------------+
|                       1 |
+-------------------------+
1 row in set (0.00 sec)

In the above example, we can see the comparison done between two strings and MySQL returns the output accordingly.

Basically, STRCMP() function can give four kinds of output after comparison −

• Output 0: MySQL STRCMP() function returns 0 if both the strings are same.
• Output 1: MySQL STRCMP() function returns 1 if the second string is smaller than the first string.
• Output -1: MySQL STRCMP() function returns -1 if the first string is smaller than the second string.
• Output NULL: MySQL STRCMP() function returns NULL if any one or both of the argument of STRCMP() function is NULL.

Example

mysql> Select STRCMP('Test', 'Test')As 'Equal Strings', STRCMP('TestABC', 'Test')AS '2nd Smaller', STRCMP('Test', 'TestABC')AS '1st Smaller', STRCMP('Test', NULL)As '2nd NULL',STRCMP(NULL, 'Test')AS '1st NULL',STRCMP(NULL,NULL)AS 'Both NULL';
+---------------+-------------+-------------+----------+----------+-----------+
| Equal Strings | 2nd Smaller | 1st Smaller | 2nd NULL | 1st NULL | Both NULL |
+---------------+-------------+-------------+----------+----------+-----------+
|       0       |      1      |      -1     |    NULL  |    NULL  |    NULL   |
+---------------+-------------+-------------+----------+----------+-----------+
1 row in set (0.00 sec)

Jai Janardhan

Published on 20-Feb-2018 16:39:07