Differentiate between two strings with different case in mysql

Question

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?

Check this question: " How can I make SQL case sensitive string comparison on MySQL? " http://stackoverflow.com/questions/5629111/how-can-i-make-sql-case-sensitive-string-comparison-on-mysql — Yousf, Oct 13 '13 at 14:34

score 1 · Accepted Answer · answered Oct 13 '13 at 14:35

1

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.

answered Oct 13 '13 at 14:35

Joni

showing error- `ERROR 1253 (42000): COLLATION 'utf8_bin' is not valid for CHARACTER SET 'latin1'` for the 1st command. 2nd one runs fine. – Rajesh Paul Oct 13 '13 at 14:45
If your client charset is set to `latin1` you can use `latin1_bin`, which is the binary collation that corresponds to the `latin1` charset. – Joni Oct 13 '13 at 15:11
thanx for navigating me with proper concept. – Rajesh Paul Oct 13 '13 at 15:27

score 1 · Answer 2 · answered Oct 13 '13 at 14:35

1

By default, MySQL is case-insensitive. Either change column collation, or use COLLATE keyword, like:

SELECT 'abc' COLLATE 'utf8_bin' = 'abc' COLLATE 'utf8_bin'

answered Oct 13 '13 at 14:35

Guillaume Poussel

It's a character set dependent answer. In my system its throwing an error- `ERROR 1253 (42000): COLLATION 'utf8_bin' is not valid for CHARACTER SET 'latin1'` – Rajesh Paul Oct 13 '13 at 14:47

score 0 · Answer 3 · answered Oct 13 '13 at 14:43

Command1

select BINARY 'aBc'='Abc';

+---------------------+
| BINARY 'aBc'= 'Abc' |
+---------------------+
|                   0 |
+---------------------+

select BINARY 'aBc'= 'aBc';

+---------------------+
| BINARY 'aBc'= 'aBc' |
+---------------------+
|                   1 |
+---------------------+