STRCMP() Function in MySQL

STRCMP() function in MySQL is used to compare two strings. If both of the strings are same then it returns 0, if the first argument is smaller than the second according to the defined order it returns -1 and it returns 1 when the second one is smaller the first one.

Syntax : STRCMP(Str1, Str2)

Parameter : This method accepts two-parameter as described below :

• str1 : It is the first string used for comparison.
• str2 : It is the second string used for comparison.

Returns : It can give four kinds of value –

• If string1 = string2, this function returns 0
• If string1 < string2, this function returns -1
• If string1 > string2, this function returns 1
• If any one or both string is NULL, this function returns NULL .

Example-1 : STRCMP() function to compare two equal string. As both given strings are equal it will return 0.

Select STRCMP('Geeks', 'Geeks') As 'Cmp_Value'

Output :

Cmp_Value
0

Example-2 : STRCMP() function to compare two string when second string is smaller than the first string. Here, the return value will be 1.

Select STRCMP('Geeks', 'Geek') As 'Cmp_Value'

Output :

Cmp_Value
1

Example-3 : STRCMP() function to compare two string when second string is bigger than the first string. As the second string is greater than the first one the result will be -1.

Select STRCMP('Geek', 'Geeks') As 'Cmp_Value'

Output :

Cmp_Value
-1

Example-4 : STRCMP() function to compare two string when at least one string is NULL.

Select STRCMP('Geek', NULL) As 'Cmp_Value'

Output :

Cmp_Value
NULL

Example-5 : STRCMP() function can also be used on column data. To demonstrate create a table named StudentDetails.

CREATE TABLE StudentDetails(

    Student_id INT AUTO_INCREMENT,  
    First_name VARCHAR(100) NOT NULL,
    Last_name VARCHAR(100) NOT NULL,
    Student_Class VARCHAR(20) NOT NULL,
    TotalExamGiven INT   NOT NULL,
    PRIMARY KEY(Student_id )

Inserting data into the Table :

INSERT INTO  
    StudentDetails(First_name, Last_name, Class, TotalExamGiven )

VALUES
    ('Sayan', 'Jana', 'IX', 8 ),
    ('Nitin', 'Sharma', 'X',  5 ),
    ('Aniket', 'Srivastava', 'XI', 6 ),
    ('Abdur', 'Ali', 'X',  7 ),
    ('Riya', 'Malakar', 'IX', 4 ),
    ('Jony', 'Patel', 'X', 10 ),
    ('Deepak', 'Saini',  'X',  7 ),
    ('Ankana', 'Biswas',  'XII', 5 ),
    ('Shreya', 'Majhi', 'X',  8 ) ;

To verify used the following command as follows.

SELECT * FROM StudentDetails;

Output :

STUDENT_ID	FIRST_NAME	LAST_NAME	CLASS	TOTALEXAMGIVEN
1	Sayan	Jana	IX	8
2	Nitin	Sharma	X	5
3	Aniket	Srivastava	XI	6
4	Abdur	Ali	X	7
5	Riya	Malakar	IX	4
6	Jony	Patel	X	10
7	Deepak	Saini	X	7
8	Ankana	Biswas	XII	5
9	Shreya	Majhi	X	8

Now, we are going to compare between First_Name and Last_Name column using STRCMP Function.

SELECT First_Name, Last_Name,
STRCMP(First_Name, Last_Name) AS Cmp_Value 
FROM StudentDetails;

Output :

FIRST_NAME	LAST_NAME	CMP_VALUE
Sayan	Jana	1
Nitin	Sharma	-1
Aniket	Srivastava	-1
Abdur	Ali	-1
Riya	Malakar	1
Jony	Patel	-1
Deepak	Saini	-1
Ankana	Biswas	-1
Shreya	Majhi	1