Skip to content
Related Articles

Related Articles

Improve Article
Save Article
Like Article

STRCMP() Function in MySQL

  • Last Updated : 11 Jan, 2021

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)

Attention reader! Don’t stop learning now. Learn SQL for interviews using SQL Course  by GeeksforGeeks.

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_IDFIRST_NAMELAST_NAMECLASSTOTALEXAMGIVEN
1SayanJanaIX8
2NitinSharmaX5
3AniketSrivastavaXI6
4AbdurAliX7
5RiyaMalakarIX4
6JonyPatelX10
7DeepakSainiX7
8AnkanaBiswasXII5
9ShreyaMajhiX8

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_NAMELAST_NAMECMP_VALUE
SayanJana1
NitinSharma-1
AniketSrivastava-1
AbdurAli-1
RiyaMalakar1
JonyPatel-1
DeepakSaini-1
AnkanaBiswas-1
ShreyaMajhi1
My Personal Notes arrow_drop_up
Recommended Articles
Page :