Make startswith and endswith case-sensitive in MySQL

[Simon Willison]

At the moment, startswith and endswith are not case-sensitive. There are two potential fixes.

In the MySQL manual ​it says this:

---

The following two statements illustrate that string comparisons are not case sensitive unless one of the operands is a binary string:

mysql> SELECT 'abc' LIKE 'ABC';
        -> 1
mysql> SELECT 'abc' LIKE BINARY 'ABC';
        -> 0

---

LIKE BINARY could solve the problem (I haven't yet tried it myself) - we would need to check which versions of MySQL it is available in though.

If that doesn't work, how about adding a mechanism to the database layer where a Python function can be defined to perform further processing on a database result set before handing it back? The MySQL backend could then define a function for startswith and endswith that runs a normal case-insesitive search and then filters the results in Python code to find only case-sensitive matches. This would allow missing functionality to be patched in to other database modules as desired.

[Adrian Holovaty]

(In [1036]) Fixed #507 -- Changed MySQL backend so that it uses 'LIKE BINARY' for case-sensitive comparisons -- contains, startswith and endswith. Thanks, Simon