Make startswith and endswith case-sensitive in MySQL
|Reported by:||Simon Willison||Owned by:||adrian|
|Component:||Database layer (models, ORM)||Version:|
|Has patch:||no||Needs documentation:||no|
|Needs tests:||no||Patch needs improvement:||no|
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.