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