distal-attribute
distal-attribute
distal-attribute
distal-attribute

operator LIKE ignores letter case

User, date Message
Written by litleo
2 years ago
Category: General
2 posts since Wed, 18 Jan 12
Hi

the LIKE operator is not working correctly:

select * from TABLE where COLUMN like '%AAA%'
will return all rows containing both 'AAA' and 'aaa'

I havent found any setting in Heidi configuration that would force it to mind the case.

I consider it a major bug of this client, ignoring letter-case in DB statements could be disastrous.
Written by ansgar
2 years ago
4789 posts since Fri, 07 Apr 06
If at all this would be the server, not the client to blame, as HeidiSQL just passes your query to the server and receives results.

But if you read the MySQL manual, you will find out that this is intentionally the case for columns with a text collation. Only columns with a binary collation will not ignore case. You can also force exact comparison:

SELECT * FROM TABLE WHERE column LIKE BINARY '%AAA%'

Written by litleo
2 years ago
2 posts since Wed, 18 Jan 12
@anse Thank you for your suggestion the "binary" clause does the trick.

I'm coming mostly from Oracle and never met this problem before - I guess it would be MySQL behavior then.
Written by kalvaro
2 years ago
563 posts since Thu, 29 Nov 07
The behaviour of text sorting and comparisons depends on the collation settings (just like in Oracle). The main difference with Oracle is that MySQL allows to set specific collations almost everywhere:

1. At server level
2. At database level
3. At table level
4. At column level
5. At connection level
6. At string level

#1 requires direct access to the server.

You'll find #2 to #4 in the appropriate "Edit" dialogues within HeidiSQL (of course, it isn't a client setting, HeidiSQL just offers a GUI to change them).

#5 can be changed from the "Host -> Variables" (find the "collation_connection" session variable).

And #6 is part of the SQL syntax, as explained in the String Literals of the MySQL manual.
 

Please login to leave a reply, or register at first.