mysql replace 不区分大小写_MySQL中不区分大小写的REPLACE？

复用功能的一个口语由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 ;

小测试：

SET @str = BINARY 'New York';

SELECT case_insensitive_replace(@str, 'y', 'K');

答案：New Kork