Case-insensitive REPLACE In MySQL?

Answer :

If replace(lower()) doesn't work, you'll need to create another function.

My 2 cents.

Since many people have upgraded from MySQL to MariaDB those people will have available a new function called REGEXP_REPLACE. Use it as you would a normal replace, but the pattern is a regular expression.

This is a working example:

UPDATE `myTable` SET `myField` = REGEXP_REPLACE(`myField`, '(?i)my insensitive string', 'new string')  WHERE `myField` REGEXP '(?i)my insensitive string' 

The option (?i) makes all the subsequent matches case insensitive (if put at the beginning of the pattern like I have then it all is insensitive).

See here for more information: https://mariadb.com/kb/en/mariadb/pcre/

Edit: as of MySQL 8.0 you can now use the regexp_replace function too, see documentation: https://dev.mysql.com/doc/refman/8.0/en/regexp.html

Alternative function for one spoken by fvox.

DELIMITER | CREATE FUNCTION case_insensitive_replace ( REPLACE_WHERE text, REPLACE_THIS text, REPLACE_WITH text ) RETURNS text DETERMINISTIC  BEGIN     DECLARE last_occurency int DEFAULT '1';      IF LCASE(REPLACE_THIS) = LCASE(REPLACE_WITH) OR LENGTH(REPLACE_THIS) < 1 THEN          RETURN REPLACE_WHERE;     END IF;      WHILE Locate( LCASE(REPLACE_THIS), LCASE(REPLACE_WHERE), last_occurency ) > 0  DO       BEGIN         SET last_occurency = Locate(LCASE(REPLACE_THIS), LCASE(REPLACE_WHERE));          SET REPLACE_WHERE = Insert( REPLACE_WHERE, last_occurency, LENGTH(REPLACE_THIS), REPLACE_WITH);          SET last_occurency = last_occurency + LENGTH(REPLACE_WITH);       END;     END WHILE;     RETURN REPLACE_WHERE; END; | DELIMITER ; 

Small test:

SET @str = BINARY 'New York'; SELECT case_insensitive_replace(@str, 'y', 'K'); 

Answers: New Kork