MacLochlainns Weblog

Sometimes you hear from some new developers that MySQL only makes case insensitive string comparisons. One of my students showed me their test case that they felt proved it:

SELECT STRCMP('a','A') WHERE 'a' = 'A';

Naturally, it returns 0, which means:

• The values compared by the STRCMP() function makes a case insensitive comparison, and
• The WHERE clause also compares strings case insensitively.

As a teacher, you’re gratified that the student took the time to build their own use cases. However, in this case I had to explain that while he was right about the STRCMP() function and the case insensitive comparison the student used in the WHERE clause was a choice, it wasn’t the only option. The student was wrong to conclude that MySQL couldn’t make case sensitive string comparisons.

I modified his sample by adding the required BINARY keyword for a case sensitive comparison in the WHERE clause:

SELECT STRCMP('a','A') WHERE BINARY 'a' = 'A';

It returns an empty set, which means the binary comparison in the WHERE clause is a case sensitive comparison. Then, I explained while the STRCMP() function performs a case insensitive match, the REPLACE() function performs a case sensitive one. Then, I gave him the following expanded use case for the two functions:

SELECT STRCMP('a','A')      AS test1
,      REPLACE('a','A','b') AS test2
,      REPLACE('a','a','b') AS test3;

It returns:

+-------+-------+-------+
| test1 | test2 | test3 |
+-------+-------+-------+
|     0 | a     | b     |
+-------+-------+-------+
1 row in set (0.00 sec)

The behavior of one function may be different than another as to how it compares strings, and its the developers responsibility to make sure they understand its behavior thoroughly before they use it. The binary comparison was a win for the student since they were building a website that needed that behavior from MySQL.

As always, I hope tidbits like this save folks time using MySQL.