When comparing strings in MySQL you find out that something is different compared with Oracle. Basicly said it’s that Oracle is case-sensitive when comparing strings and MySQL is not. Let’s say you have a table TBL_TEST that looks like this:
| TBL_TEST | ||
| ID | Name | City |
| 1 | Schmidt | kiel |
| 2 | Maier | KIEL |
Given the following SQL:
SELECT *
FROM TBL_TEST
WHERE City=‘kiel‘;
In MySQL you receive both rows as the string comparison is not case-sensitive. In Oracle and MS SQL you would receive only the first row. It is the same if you use the LIKE operator.
So how will you accomplish a case sensitive comparison in MySQL then?
The trick is to use the operator binary in your equation. That converts a normal string into a binary string and the result is that the strings are compared by character code rather than by character. The SQL changes like this:
SELECT *
FROM TBL_TEST
WHERE City=binary ‘kiel‘;
Now in MySQL you also only receive the first row. The binary operator can be placed on both sides, the effect is always the same. By having one binary string in your equation the strings are compared on byte level.
I hope this helps and explains this behaviour a little bit.
If you are interested in this or other advanced Cognos/SQL topics you can also attend training on my corporate homepage. At the moment my open courses are only available in German. Inhouse training and online courses are also available in English language. So check out my open trainings (German) or my online courses (English)!
