Case Insensitive REPLACE() for MySQL

One request I occasionally see is for a case insensitive version of REPLACE() for MySQL. I wrote this a while back, but here it is now for all of you to play around with. It uses a basic naïve string search algorithm, so can be slow under some circumstances.
[code lang=”sql”]DELIMITER $$

DROP FUNCTION IF EXISTS `replace_ci`$$
CREATE FUNCTION `replace_ci` ( str TEXT,needle CHAR(255),str_rep CHAR(255))
RETURNS TEXT
DETERMINISTIC
BEGIN
DECLARE return_str TEXT DEFAULT ”;
DECLARE lower_str TEXT;
DECLARE lower_needle TEXT;
DECLARE pos INT DEFAULT 1;
DECLARE old_pos INT DEFAULT 1;

IF needle = ” THEN
RETURN str;
END IF;

SELECT lower(str) INTO lower_str;
SELECT lower(needle) INTO lower_needle;
SELECT locate(lower_needle, lower_str, pos) INTO pos;
WHILE pos > 0 DO
SELECT concat(return_str, substr(str, old_pos, pos-old_pos), str_rep) INTO return_str;
SELECT pos + char_length(needle) INTO pos;
SELECT pos INTO old_pos;
SELECT locate(lower_needle, lower_str, pos) INTO pos;
END WHILE;
SELECT concat(return_str, substr(str, old_pos, char_length(str))) INTO return_str;
RETURN return_str;
END$$

DELIMITER ;

SELECT replace_ci( ‘mySQL’, ‘M’, ‘M’ );[/code]
It’s also available on MySQL Forge.

8 comments

  2. hi, thanks for this useful post, i already move my blog to another domain and realize that all images on my post still pointing to old domain, so i need to find and replace all url on my post with my new domain. Thanks.

  3. Great post! I found a bug you may want to fix. If you’re using strings with spaces on both sides, it seems that when you declare the variables as CHAR() it truncates the last space! For example, try this:

    select replace_ci(‘ Cole Hayboy ‘, ‘ CO ‘, ”) from dual;

    There are spaces on both sides of the str and needle. The output should be the same as the input with no replacements, because of the trailing space in the needle. However, because the trailing space of the ‘ CO ‘ needle parameter is truncated, it finds a match and the output is “ole Hayboy “.

    If you change the data types for the needle and str_rep parameters to VARCHAR instead of CHAR, the function works as expected. It’s very bizarre, and may be an underlying MySQL bug. It took me forever to figure out why things were getting replaced that shouldn’t. I hope this helps someone else some day. 😀

  5. This works well. But I am looking for a function that will replace & maintain the case. For e.g. colour should be replaced with color & Colour with Color. Is this possible? Can anyone help?

  6. thanks for this, please fix the following disaster bug: if you pass an empty string in the needle, the function gets stuck in an infinite loop, i spent 3 days troubleshooting this! , mysql server went down cos of the locks generated by this!!!!

    put “and needle” ” in the while statement

    thanks!

Comments are closed.