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 12:09:07

• Related Questions & Answers
• What is strcmp() Function in C language?
• What would be the effect on the output of MySQL LAST_INSERT_ID() function in the case on multiple-row insert?
• What would be the output of MySQL ELT() function if the index number, provided as an argument, is not an integer?
• What would be the output of MySQL SUM() function if a column having no values has been passed as its argument?
• What would be effect of negative value of second argument, which specifies the number of decimal places, on the output of MySQL ROUND() function?
• What would be effect of negative value of second argument, which specifies the number of decimal places, on the output of MySQL TRUNCATE() function?
• How number values be used as arguments in MySQL STRCMP() function?
• While linking the strings, if I will add a NULL value then what would be the output of a CONCAT_WS() function?
• What kind of output is produced by UNIX_TIMESTAMP() function?
• What would be output if we will try to extract time values by providing the date values only to MySQL EXTRACT() function?
• strcmp() function in PHP
• While linking two strings, if I will add a NULL value then what would be the output of a CONCAT() function?
• What would be the effect on MySQL output if we have the combination of NULL and other values in the list of strings, provided as arguments in FIELD() function?
• How can MySQL COALESCE() function be used with MySQL SUM() function to customize the output?
• What is the use of MySQL FROM_UNIXTIME() function?