mysql - how to update and replace ignoring case

Question

How to update columns - case insensitive ??

If I run the query below it brings back 62 records as expected:

select entity_id 
from field_data_body 
where body_value like '%mailto:[email protected]%';

Returns 62 Records

I am trying to update these records so that [email protected] is replaced with http://iss.servicedesk.example.com using the following query:

update field_data_body
SET body_value = REPLACE(body_value,'%mailto:[email protected]%',
                        'http://iss.servicedesk.example.com');

Unfortunately it is only updating 52 records as it is running a case sensitive query e.g. [email protected] is not recognized in the above query.

How can I run the above update query but make it case insensitive to pick up all tables I am wishing to update ?

I have updated the query below - its runs but does not find any results to update when I know there are links that need updating :

update `field_data_body` SET `body_value` = REPLACE(body_value,'%mailto:[email protected]%','https://iss.servicedesk.example.com')
where LOWER(CONVERT( body_value USING latin1)) like '%mailto:[email protected]%'

Any advise on what I need to do to fix it so it runs correctly ?

Answer 1

Use UPPER() or LOWER() function with the query.

Answer 2

just an FYI: LOWER and UPPER dont work on BINARY, VARBINARY and BLOB. If your field is of one of these type, you'll need to convert the string to a nonbinary string by using CONVERT:

 LOWER(CONVERT(@str USING latin1))