Case insensitive replacement into mariadb sql

UNIX for Beginners Q & A
1

Hello,
I am running mariadb under ubuntu20 and I am trying to set value where criteria is matched but it fails since I am unable to make it case-insensitive.
I tried Binary, Regexp Binary. Some says "you should use VARBINARY data type or _bin collation but no much clue regarding how to solve this issue in here

My code:
update source set source_icon = "https:\/\/i.imgur.com\/eZpvJzJ.png" where BINARY source_display_name like 'PT%CANAL*PANDA%';
Shown name in mariadb is PT: Canal Panda

Thanks
Boris

2

Hello,

It's been a little while now since I routinely administered MySQL on a daily basis, but from what I recall the default collation (latin1_swedish_ci) is case insensitive - indeed, that's what the "_ci" at the end of the collation's name signifies. A quick check on my own CentOS 7 test system seems to show that I can use lower case, upper case or any combination of the two in a SELECT or UPDATE statement and it works as expected, finding whatever's there regardless of case.

I would presume then that you are using a non-case-insensitive (case sensitive, in other words) collation. If you are, then you might want to switch to one of the case-insensitive ones, unless you have a valid application-specific reason not to do that, of course.

3

I'm confused a bit. Do you want to do the "matching" case-INsensitive" or "case-sensitive"?

Looks like your example (and rightly so) does the case-INsensitive match which is the correct default behavior described here.

I'm no mariadb expert, so...

4

Hi @baris35,

a typical way to compare case insensitive is both sides e.g. to convert to lowercase:

update tbl set col = foo where lower(col) rlike 'canal panda;

Here the right hand side must be lowercase.
Note the (big) difference between wildcards % and regexes. Here, canal panda is a regex, but a very simple one, without any special chars like *or [] etc. See e.g. https://dev.mysql.com/doc/refman/8.0/en/pattern-matching.html.

5

Thank you @bendingrodriguez and @vgersh99 ,
When I change my script, below way gave the expected output .
..... where LOWER(source_display_name) like LOWER('PT%$newC1%');"

I learnt a bit about lower command in sql today.
Even if the keywords are typed with uppercase, it give what I needed. Also I was not aware that sql patterns are searched case-insensitive by default.
Thank you for the link

Kind regards
Boris