STRCMP String comparison two strings MySQL Query

SELECT STRCMP(str1,str2)

str1 and str2 are two input strings to be compared.
Output is here

-1

if str1 is smaller than str2

if str1 is equal to str2

if str1 is greater than str2

We can compare two columns of a student table.

SELECT STRCMP( f_name, l_name ) , f_name, l_name FROM  `student_name`

Handling NULL data

If any of the string is null then output became NULL. Here is the output of above query.

STRCMP( f_name, l_name )	f_name	l_name
1	John	Deo
NULL	Larry	NULL
NULL	Ronald	NULL
-1	Garry	Miller
NULL	NULL	NULL
NULL	NULL	Ruller
0	Alex	alex

Using ifnull we can replace null data with a fixed string for our STRCMP comparison.

SELECT STRCMP( ifnull(f_name, 'not known'), 
ifnull(l_name,'not known') ) , f_name, l_name FROM  `student_name`

We can remove all strings ( used in comparison ) having null data. We will use WHERE condition check with AND combination.

SELECT STRCMP( f_name, l_name ) , f_name, l_name 
FROM  `student_name`  WHERE f_name is not null AND l_name is not null

STRCMP( f_name, l_name )	f_name	l_name
1	John	Deo
-1	Garry	Miller
0	Alex	alex

NULL safe operator <=>

For any comparison we can include NULL values by using NULL safe operator <=>
NULL Value & Null safe operator

Case sensitive comparison

You can see in above display , STRCMP returns 0 ( both matching ) for comparison between strings Alex and alex. To make the comparison case sensitive we can use BINARY comparison. By using BINARY we are comparing byte by byte rather than character by character

SELECT STRCMP( BINARY f_name, BINARY l_name ) , 
f_name, l_name FROM  `student_name` 
WHERE f_name IS NOT NULL AND l_name IS NOT NULL

STRCMP( BINARY f_name, BINARY l_name )	f_name	l_name
1	John	Deo
-1	Garry	Miller
-1	Alex	alex

Using Character Set and COLLATE

SELECT STRCMP( f_name COLLATE utf8_bin, l_name COLLATE utf8_bin ) , 
f_name, l_name FROM  `student_name` 
WHERE f_name IS NOT NULL AND l_name IS NOT NULL

STRCMP( f_name COLLATE utf8_bin, l_name COLLATE utf8_bin )	f_name	l_name
1	John	Deo
-1	Garry	Miller
-1	Alex	alex

SQL Dump of student_name table

CREATE TABLE IF NOT EXISTS `student_name` (
  `f_name` varchar(20) DEFAULT NULL,
  `l_name` varchar(20) DEFAULT NULL,
  `class` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `student_name`
--

INSERT INTO `student_name` (`f_name`, `l_name`, `class`) VALUES
('John', 'Deo', 'Four'),
('Larry', NULL, 'Four'),
('Ronald', NULL, 'Five'),
('Garry', 'Miller', 'Five'),
(NULL, NULL, 'Five'),
(NULL, 'Ruller', NULL),
('Alex', 'alex', 'Four');

SQL String References FIELD to get position of string

STRCMP : String comparison

Handling NULL data

NULL safe operator <=>

Case sensitive comparison

Using Character Set and COLLATE

Subscribe