Consider the following sql command run in MySQL command prompt-
select 'aBc'='Abc';
O/P
+-------------+
| 'aBc'='Abc' |
+-------------+
| 1 |
+-------------+
I expected the result to show '0' in place of '1'.
How can I differentiate between two strings if they are not in the same case?
You can use a binary collation. For example:
select 'aBc'='Abc' collate utf8_bin;
Or you can transform one of the strings into a binary type:
select binary('aBc')=binary('Abc');
For the differences between these two, see The _bin and binary Collations in the MySQL documentation.
ERROR 1253 (42000): COLLATION 'utf8_bin' is not valid for CHARACTER SET 'latin1' for the 1st command. 2nd one runs fine.
latin1 you can use latin1_bin, which is the binary collation that corresponds to the latin1 charset.By default, MySQL is case-insensitive.
Either change column collation, or use COLLATE keyword, like:
SELECT 'abc' COLLATE 'utf8_bin' = 'abc' COLLATE 'utf8_bin'
ERROR 1253 (42000): COLLATION 'utf8_bin' is not valid for CHARACTER SET 'latin1'select BINARY 'aBc'='Abc';
+---------------------+
| BINARY 'aBc'= 'Abc' |
+---------------------+
| 0 |
+---------------------+
select BINARY 'aBc'= 'aBc';
+---------------------+
| BINARY 'aBc'= 'aBc' |
+---------------------+
| 1 |
+---------------------+