如何从 MySQL 中的字符串中删除所有非字母数字字符?

2021-11-20 00:00:00 string regex mysql alphanumeric

我正在研究一个比较字符串的例程，但为了提高效率，我需要删除所有不是字母或数字的字符.

I'm working on a routine that compares strings, but for better efficiency I need to remove all characters that are not letters or numbers.

我现在使用多个 REPLACE 函数，但也许有更快更好的解决方案?

I'm using multiple REPLACE functions now, but maybe there is a faster and nicer solution ?

推荐答案

使用 MySQL 8.0 或更高版本

由下面 michal.jakubeczy 的回答提供，MySQL 现在支持用 Regex 替换:

Using MySQL 8.0 or higher

Courtesy of michal.jakubeczy's answer below, replacing by Regex is now supported by MySQL:

UPDATE {table} SET {column} = REGEXP_REPLACE({column}, '[^0-9a-zA-Z ]', '')

使用 MySQL 5.7 或更低版本

此处不支持正则表达式.我必须创建自己的名为 alphanum 的函数，它为我去除了字符:

Using MySQL 5.7 or lower

Regex isn't supported here. I had to create my own function called alphanum which stripped the chars for me:

DROP FUNCTION IF EXISTS alphanum; 
DELIMITER | 
CREATE FUNCTION alphanum( str CHAR(255) ) RETURNS CHAR(255) DETERMINISTIC
BEGIN 
  DECLARE i, len SMALLINT DEFAULT 1; 
  DECLARE ret CHAR(255) DEFAULT ''; 
  DECLARE c CHAR(1);
  IF str IS NOT NULL THEN 
    SET len = CHAR_LENGTH( str ); 
    REPEAT 
      BEGIN 
        SET c = MID( str, i, 1 ); 
        IF c REGEXP '[[:alnum:]]' THEN 
          SET ret=CONCAT(ret,c); 
        END IF; 
        SET i = i + 1; 
      END; 
    UNTIL i > len END REPEAT; 
  ELSE
    SET ret='';
  END IF;
  RETURN ret; 
END | 
DELIMITER ;

现在我可以:

select 'This works finally!', alphanum('This works finally!');

我得到:

+---------------------+---------------------------------+
| This works finally! | alphanum('This works finally!') |
+---------------------+---------------------------------+
| This works finally! | Thisworksfinally                |
+---------------------+---------------------------------+
1 row in set (0.00 sec)

万岁！

相关文章