STRCMP String comparison two strings MySQL Query

SELECT STRCMP('apple', 'banana'); -- Output: -1 (apple < banana)
SELECT STRCMP('banana', 'apple'); -- Output: 1 (banana > apple)
SELECT STRCMP('apple', 'apple');  -- Output: 0 (apple = apple)

Syntax

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

STRCMP() follows the collation of its string arguments. With a typical case-insensitive collation, values such as 'APPLE' and 'apple' compare as equal. Use a binary string or a case-sensitive collation when case must matter.

SELECT STRCMP('APPLE', 'apple');  -- Output: 0

For an explicitly case-sensitive comparison, use a binary expression or a case-sensitive collation.

SELECT 'APPLE' = BINARY 'apple'; -- Output 0 ( False )

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. Using a binary expression makes the comparison byte-sensitive, so uppercase and lowercase bytes compare differently.

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 utf8mb4_bin, l_name COLLATE utf8mb4_bin ) , 
f_name, l_name FROM  `student_name` 
WHERE f_name IS NOT NULL AND l_name IS NOT NULL

STRCMP( f_name COLLATE utf8mb4_bin, l_name COLLATE utf8mb4_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=utf8mb4;

--
-- 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');

FIELD to get position of string

All String Functions FIELD()

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author Watch on YouTube

Connect:

Subscribe to our YouTube Channel here

STRCMP : String comparison

Handling NULL data

NULL safe operator <=>

Case sensitive comparison

Using Character Set and COLLATE

Subhendu Mohapatra