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 :

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 :

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 :

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

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

Output :

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 :

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 :

jana_sayantan

Follow

Improve

Article Tags :

• DBMS
• SQL
• mysql