SQL select case insensitive text but not Latex function names

Question

I have a MySql database of latex snippets. Each snippet contains normal text and latex commands. The commands are all preceded by a backslash \ . I would like to search through these snippets such that the text is case insensitive but the commands are case insensitive. So selecting for vector gives results where the text contains either vector or Vector whereas selecting for \Vector will not return \vector.

Answer 1

Your question is about collations. A column in a table has a collation setting, for example utfmb4_general_ci or utf8mb4_bin. The first of those is case-insensitive, meaning a search like this

    WHERE col LIKE '%vector%'

will yield rows containing both ...Vector... and ...vector...

If you use the utf8_bin (binary match, case sensitive) collation, that search excludes ...Vector...

You can specify the collation to use in a filter clause, like so:

   WHERE col LIKE '%Vector%'  COLLATE utf8_bin;

and that will force MySQL to use the collation you want. If you don't specificy the collation (the normal case) MySQL uses the collation specified at the time you created the table or the column.

When you specify a collation explicitly, and it's different from the column's collation, MySQL's query planner cannot use an index on the column to satisfy your query. So it might be slower. Of course the filter-clause pattern column LIKE '%value%' (with a leading %) also prevents the use the index.