Bug #12940 UCASE an LCASE in a WHERE query

• Submitted: 1 Sep 2005 23:19
• Modified: 2 Sep 2005 3:35
• Reporter: Vryand Fireheart
• Status: Closed
• Category: MySQL Server
• Severity: S3 (Non-critical)
• CPU Architecture: Any

[1 Sep 2005 23:19] Vryand Fireheart

Description:
OK... This is the 1st time I post.
I have this Problem with UCASE() and LCASE(),
My table is like this:

`TABLE`
+-----------+-----------------+
| id (int)  | Code (tinytext) |
+-----------+-----------------+
| 1         | NADA            |
| 2         | thing           |
| 3         | AnY             |
+-----------+-----------------+

SELECT id FROM `TABLE` WHERE LCASE(`code`)='nada';
RETURNS (1 Record): id=1
SELECT id FROM `TABLE` WHERE UCASE(`code`)='nada';
RETURNS (1 Record): id=1

SELECT id FROM `TABLE` WHERE LCASE(`code`)='THING';
RETURNS (1 Record): id=2
SELECT id FROM `TABLE` WHERE UCASE(`code`)='THING';
RETURNS (1 Record): id=2

SELECT id FROM `TABLE` WHERE LCASE(`code`)='aNy';
RETURNS (1 Record): id=3
SELECT id FROM `TABLE` WHERE UCASE(`code`)='aNy';
RETURNS (1 Record): id=3

I do not want to change the results in PHP to RE-COMPARE the results (Double work).

Does any one know how to do a real CASE COMPARISON with a QUERY inside MySQL ?

(I do not see it) :(

How to repeat:
SAME way

Suggested fix:
OK... This is the 1st time I post.
I have this Problem with UCASE() and LCASE(),
My table is like this:

`TABLE`
+-----------+-----------------+
| id (int)  | Code (tinytext) |
+-----------+-----------------+
| 1         | NADA            |
| 2         | thing           |
| 3         | AnY             |
+-----------+-----------------+

SELECT id FROM `TABLE` WHERE LCASE(`code`)='nada';
RETURNS (1 Record): id=1
SELECT id FROM `TABLE` WHERE UCASE(`code`)='nada';
RETURNS (0 Record)

SELECT id FROM `TABLE` WHERE LCASE(`code`)='THING';
RETURNS (0 Record)
SELECT id FROM `TABLE` WHERE UCASE(`code`)='THING';
RETURNS (1 Record): id=2

SELECT id FROM `TABLE` WHERE LCASE(`code`)='aNy';
RETURNS (0 Record)
SELECT id FROM `TABLE` WHERE UCASE(`code`)='aNy';
RETURNS (0 Record)

[2 Sep 2005 3:35] Jorge del Conde

We're sorry, but the bug system is not the appropriate forum for 
asking help on using MySQL products. Your problem is not the result 
of a bug.

Support on using our products is available both free in our forums
at http://forums.mysql.com and for a reasonable fee direct from our
skilled support engineers at http://www.mysql.com/support/

Thank you for your interest in MySQL.

Additional info:

This might help you:

mysql> select * from t1 where LCASE(code) = BINARY('nada');