SELECT STRCMP(str1,str2)
str1 and str2 are two input strings to be compared.
Output is here
-1
if str1 is smaller than str2
0
if str1 is equal to str2
+1
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 comparision 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 →