operator LIKE ignores letter case

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.

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%'

@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.

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.