Why does column = upper(column) work in Oracle but not in MySQL?

Question

In mySQL I have to use WHERE BINARY UPPER(col1) = col1 but in Oracle it works with no problem. Can someone explain to me why this is? I am new to mySQL.

So looking at it with the following statement

select upper(col1),col1
from mytable
where upper(col1) = col1;

Result in Oracle:

UPPER(COL1)          COL1               
-------------------- --------------------
JÜRGEN               JÜRGEN               
RENÉ                 RENÉ                 
CARL                 CARL

Result in MySQL

UPPER(COL1)          COL1               
-------------------- --------------------
JÜRGEN               JÜRGEN               
JÜRGEN               Jürgen               
RENÉ                 René                 
RENÉ                 RENÉ                 
CARL                 Carl                 
CARL                 CARL

score 4 · Accepted Answer · edited May 23 '17 at 10:34

4

String comparisons are not case-sensitive in MySQL nor SQLServer by default.

How can I make SQL case sensitive string comparison on MySQL?

http://dev.mysql.com/doc/refman/5.0/en/case-sensitivity.html

Why is SQL Server '=' comparator case insensitive?

http://msdn.microsoft.com/en-us/library/aa174903%28SQL.80%29.aspx

edited May 23 '17 at 10:34

Community

1
1

answered Aug 23 '12 at 20:21

Matthew

10,244
5
49
104

score 3 · Answer 2 · answered Aug 23 '12 at 20:21

3

Your MySQL collation is case-insensitive... so 'CARL' = 'Carl' is true.

answered Aug 23 '12 at 20:21

EthanB

4,239
1
28
46

score 1 · Answer 3 · answered Aug 23 '12 at 20:57

1

Place the word BINARY between your WHERE and UPPER. That will force a case sensitive comparison

answered Aug 23 '12 at 20:57

Tim

11
1

Why does column = upper(column) work in Oracle but not in MySQL?

3 Answers3