MySQL中大小写不敏感的REPLACE函数？

Question

MySQL在默认排序规则下运行几乎所有的字符串比较... 除了REPLACE命令。我使用不区分大小写的排序规则，需要运行不区分大小写的REPLACE。有没有办法强制REPLACE使用当前的排序规则而不总是进行区分大小写的比较？我愿意升级我的MySQL（目前运行的是5.1）以获得更多功能...

mysql> charset utf8 collation utf8_unicode_ci;
Charset changed

mysql> select 'abc' like '%B%';
+------------------+
| 'abc' like '%B%' |
+------------------+
|                1 |
+------------------+

mysql> select replace('aAbBcC', 'a', 'f');
+-----------------------------+
| replace('aAbBcC', 'a', 'f') |
+-----------------------------+
| fAbBcC                      |   <--- *NOT* 'ffbBcC'
+-----------------------------+

- dkarp

Answer 1

如果 replace(lower()) 不起作用，你需要创建 另一个函数。

- fvox

---

我需要保留原始字符串中未替换部分的大小写，所以不可以。 - dkarp

UPDATE repSchedule SET Email=REPLACE ( LOWER(email), '@xyz.com','@xxyyzz.co.uk') - zzapper

Answer 2

我的两分钱。

由于许多人已经从MySQL迁移到了MariaDB，这些人将会有一个名为REGEXP_REPLACE的新函数可用。像普通替换一样使用它，但是模式是正则表达式。

这是一个实际示例：

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

(?i)选项将所有后续匹配设置为不区分大小写(如果像我一样在模式的开头放置它，则全部不区分大小写)。

有关更多信息，请参见此处：https://mariadb.com/kb/en/mariadb/pcre/

编辑：从MySQL 8.0开始，您现在也可以使用regexp_replace函数，详情请参考文档：https://dev.mysql.com/doc/refman/8.0/en/regexp.html

- santiago arizti

---

在这种特定情况下，“where…”部分并不是必需的，因为该函数只会在找到匹配项时进行替换，但我认为指出正则表达式也可以用于where条件是很好的。顺便说一句，在普通的mysql中支持在where条件中使用正则表达式，但不支持regexp_replace。 - santiago arizti

Answer 3

一个由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');

答案： 新科克

- Aivar

Answer 4

这个修改版的Luist答案允许你用大小写不同的版本替换针头（只需更改两行代码）。

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 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), last_occurency);
      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 ;

- notalbert

Answer 5

在之前的回答和pento.net链接中，LOCATE()的参数是小写的。

这是一种资源浪费，因为LOCATE默认是不区分大小写的：

mysql> select locate('el', 'HELLo');
+-----------------------+
| locate('el', 'HELLo') |
+-----------------------+
|                     2 |
+-----------------------+

您可以将

WHILE Locate( LCASE(REPLACE_THIS), LCASE(REPLACE_WHERE), last_occurency ) > 0 DO

替换为

WHILE Locate(REPLACE_THIS, REPLACE_WHERE, last_occurency ) > 0 DO

等等。

- fisharebest

Answer 6

在处理“特殊”字符时，会出现意外的行为：

SELECT case_insensitive_replace('A', 'Ã', 'a')

提供

a

这是出乎意料的...因为我们只想替换Ã而不是A

更奇怪的是：

SELECT LOCATE('Ã', 'A');

提供

0

哪个是正确的结果...似乎与存储过程参数的编码有关...

- iwritecode

Answer 7

我选择了http://pento.net/2009/02/15/case-insensitive-replace-for-mysql/（在fvox的回答中），它执行不区分大小写的搜索，进行区分大小写的替换，并且不改变搜索字符串中其他未受影响字符的大小写。

注意：同一页下方的评论指出，CHAR(255) 应更改为 VARCHAR(255) - 我也需要这样做。

- Wandering Zombie

Answer 8

我喜欢使用我创建的搜索和替换函数，当我需要替换而不用担心原始字符串或搜索字符串的大小写时。如果您传入空/ null搜索字符串或null替换字符串而不更改传入字符串，则此例程会快速退出。我还添加了一个安全倒计时，以防搜索保持循环。这样我们就不会永远陷在循环中。如果您认为起始数字太低，请更改它。

delimiter //

DROP FUNCTION IF EXISTS `replace_nocase`//

CREATE FUNCTION `replace_nocase`(raw text, find_str varchar(1000), replace_str varchar(1000)) RETURNS text
CHARACTER SET utf8
DETERMINISTIC
BEGIN
    declare ret text;
    declare len int;
    declare hit int;
    declare safe int;
    
    if find_str is null or find_str='' or replace_str is null then
        return raw;
    end if;

    set safe=10000;
    set ret=raw;
    set len=length(find_str);
    
    set hit=LOCATE(find_str,ret);
    while hit>0 and safe>0 do
        set ret=concat(substring(ret,1,hit-1),replace_str,substring(ret,hit+len));
        set hit=LOCATE(find_str,ret,hit+1);
        set safe=safe-1;
    end while;
    

    return ret;
END//

- Glenn J. Schworak

Answer 9

这个问题有点老了，但我遇到了同样的问题，而且给出的答案并没有完全解决它。

我希望结果保留原始字符串的大小写。

因此，我对fvox提出的replace_ci函数进行了小修改：

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 tmp_needle TEXT;
DECLARE str_origin_char CHAR(1);
DECLARE str_rep_char CHAR(1);
DECLARE final_str_rep TEXT DEFAULT '';
DECLARE pos INT DEFAULT 1;
DECLARE old_pos INT DEFAULT 1;
DECLARE needle_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 substr(str, pos, char_length(needle)) INTO tmp_needle;
    SELECT '' INTO final_str_rep;
    SELECT 1 INTO needle_pos;
    WHILE needle_pos <= char_length(tmp_needle) DO
        SELECT substr(tmp_needle, needle_pos, 1) INTO str_origin_char;
        SELECT SUBSTR(str_rep, needle_pos, 1) INTO str_rep_char;
        SELECT CONCAT(final_str_rep, IF(BINARY str_origin_char = LOWER(str_origin_char), LOWER(str_rep_char), IF(BINARY str_origin_char = UPPER(str_origin_char), UPPER(str_rep_char), str_rep_char))) INTO final_str_rep;
        SELECT (needle_pos + 1) INTO needle_pos;
    END WHILE;
    SELECT CONCAT(return_str, SUBSTR(str, old_pos, pos - old_pos), final_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', 'e' ) as replaced;

将返回: | 替换 | | --- | | EySQL |

- Vincent F