STRCMP : String comparison

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_namel_name
1JohnDeo
NULLLarryNULL
NULLRonaldNULL
-1GarryMiller
NULLNULLNULL
NULLNULLRuller
0Alexalex
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_namel_name
1JohnDeo
-1GarryMiller
0Alexalex

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_namel_name
1JohnDeo
-1GarryMiller
-1Alexalex

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_namel_name
1JohnDeo
-1GarryMiller
-1Alexalex
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');

Your Rating




Google+

Post Comment This is for short comments only. Use the forum for more discussions.








HTML MySQL PHP JavaScript ASP Photoshop Articles FORUM . Contact us
©2000-2019 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer