如何在MySQL中从字符串中删除所有非字母数字字符？

Question

我正在编写一个比较字符串的程序，为了提高效率，我需要删除所有不是字母或数字的字符。

目前我正在使用多个 REPLACE 函数，但也许有更快捷、更好的解决方案？

- Dylan

Answer 1

使用 MySQL 8.0 或更高版本

感谢 michal.jakubeczy 的下面的回答，MySQL 现在支持使用正则表达式进行替换：

UPDATE {table} SET {column} = REGEXP_REPLACE({column}, '[^0-9a-zÀ-ÿ ]', '')

使用MySQL 5.7或更低版本

这里不支持正则表达式。我不得不创建自己的函数，称为alphanum，以帮助我剥离字符：

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)

太棒了！

- Ryan Shillington

---

你的正则表达式没有包含带重音符号的字符。 使用[^-'0-9a-zÀ-ÿ]]*应该可以解决大多数语言的问题。 更多信息请参见：https://dev59.com/E2Af5IYBdhLWcg3wbCJ3 - 8oris

澄清一下（我一开始没明白）- @Boris 的意思是8.0解决方案，'[[:alnum:]]在5.7解决方案中运行良好。顺便说一句 - 非常感谢您的分享！ - Kuba D

我更新了答案@8oris和Kuba D - Ryan Shillington

Answer 2

从性能的角度考虑，（并且假设您更多地阅读而不是编写）

我认为最好的方法是预先计算和存储列的剥离版本，这样您可以减少转换次数。

然后，您可以在新列上放置索引，让数据库为您完成工作。

- Kevin Burton

Answer 3

自MySQL 8.0起，您可以使用正则表达式从字符串中删除非字母数字字符。有方法REGEXP_REPLACE。

以下是删除非字母数字字符的代码：

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

- michal.jakubeczy

Answer 4

SELECT teststring REGEXP '[[:alnum:]]+';

SELECT * FROM testtable WHERE test REGEXP '[[:alnum:]]+'; 

参见: http://dev.mysql.com/doc/refman/5.1/en/regexp.html
下滑到这个部分: [:character_class:]

如果你想要最快的方式来处理字符串，建议使用 str_udf，请看：
https://github.com/hholzgra/mysql-udf-regexp

- Johan

---

MySQL的正则表达式只能查找数据，无法进行替换，因此这对提问者没有任何帮助。 - Marc B

在MySQL 8中，以下语句有效：SELECT REGEXP_REPLACE('1A=2b+ 3C', '[[:alnum:]]', '') - Ben

Answer 5

针对拉丁文和西里尔文字符的直接且经过考验的解决方案:

DELIMITER //

CREATE FUNCTION `remove_non_numeric_and_letters`(input TEXT)
  RETURNS TEXT
  BEGIN
    DECLARE output TEXT DEFAULT '';
    DECLARE iterator INT DEFAULT 1;
    WHILE iterator < (LENGTH(input) + 1) DO
      IF SUBSTRING(input, iterator, 1) IN
         ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9', 'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z', 'a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i', 'j', 'k', 'l', 'm', 'n', 'o', 'p', 'q', 'r', 's', 't', 'u', 'v', 'w', 'x', 'y', 'z', 'А', 'Б', 'В', 'Г', 'Д', 'Е', 'Ж', 'З', 'И', 'Й', 'К', 'Л', 'М', 'Н', 'О', 'П', 'Р', 'С', 'Т', 'У', 'Ф', 'Х', 'Ц', 'Ч', 'Ш', 'Щ', 'Ъ', 'Ы', 'Ь', 'Э', 'Ю', 'Я', 'а', 'б', 'в', 'г', 'д', 'е', 'ж', 'з', 'и', 'й', 'к', 'л', 'м', 'н', 'о', 'п', 'р', 'с', 'т', 'у', 'ф', 'х', 'ц', 'ч', 'ш', 'щ', 'ъ', 'ы', 'ь', 'э', 'ю', 'я')
      THEN
        SET output = CONCAT(output, SUBSTRING(input, iterator, 1));
      END IF;
      SET iterator = iterator + 1;
    END WHILE;
    RETURN output;
  END //

DELIMITER ;

使用方法：

-- outputs "hello12356"
SELECT remove_non_numeric_and_letters('hello - 12356-привет ""]')

- userlond

Answer 6

基于Ryan Shillington的答案进行修改，使其适用于长度超过255个字符且保留原始字符串中的空格。

顺便提一下，在最后使用了lower(str)。

我使用这个来比较字符串：

DROP FUNCTION IF EXISTS spacealphanum;
DELIMITER $$
CREATE FUNCTION `spacealphanum`( str TEXT ) RETURNS TEXT CHARSET utf8
BEGIN 
  DECLARE i, len SMALLINT DEFAULT 1; 
  DECLARE ret TEXT DEFAULT ''; 
  DECLARE c CHAR(1); 
  SET len = CHAR_LENGTH( str ); 
  REPEAT 
    BEGIN 
      SET c = MID( str, i, 1 ); 
      IF c REGEXP '[[:alnum:]]' THEN 
        SET ret=CONCAT(ret,c); 
      ELSEIF  c = ' ' THEN
          SET ret=CONCAT(ret," ");
      END IF; 
      SET i = i + 1; 
    END; 
  UNTIL i > len END REPEAT; 
  SET ret = lower(ret);
  RETURN ret; 
  END $$
  DELIMITER ;

- Alon Asulin

Answer 7

我能找到的最快的方法（并使用）是通过 convert(). 实现的。

从 CONVERT() 文档中可以看出，USING 用于在不同字符集之间转换数据。

示例：

convert(string USING ascii)

在您的情况下，正确的字符集将是自定义。

注：从文档中可以看出，CONVERT()的USING表单可在4.1.0及以上版本中使用。

- vdd

Answer 8

我写了这个UDF。但是，它只能去除字符串开头的特殊字符。它还会将字符串转换为小写字母。如果需要，您可以更新此函数。

DELIMITER //

DROP FUNCTION IF EXISTS DELETE_DOUBLE_SPACES//

CREATE FUNCTION DELETE_DOUBLE_SPACES ( title VARCHAR(250) )
RETURNS VARCHAR(250) DETERMINISTIC
BEGIN
    DECLARE result VARCHAR(250);
    SET result = REPLACE( title, '  ', ' ' );
    WHILE (result <> title) DO 
        SET title = result;
        SET result = REPLACE( title, '  ', ' ' );
    END WHILE;
    RETURN result;
END//

DROP FUNCTION IF EXISTS LFILTER//

CREATE FUNCTION LFILTER ( title VARCHAR(250) )
RETURNS VARCHAR(250) DETERMINISTIC
BEGIN
    WHILE (1=1) DO
        IF(  ASCII(title) BETWEEN ASCII('a') AND ASCII('z')
            OR ASCII(title) BETWEEN ASCII('A') AND ASCII('Z')
            OR ASCII(title) BETWEEN ASCII('0') AND ASCII('9')
        ) THEN
            SET title = LOWER( title );
            SET title = REPLACE(
                REPLACE(
                    REPLACE(
                        title,
                        CHAR(10), ' '
                    ),
                    CHAR(13), ' '
                ) ,
                CHAR(9), ' '
            );
            SET title = DELETE_DOUBLE_SPACES( title );
            RETURN title;
        ELSE
            SET title = SUBSTRING( title, 2 );          
        END IF;
    END WHILE;
END//
DELIMITER ;

SELECT LFILTER(' !@#$%^&*()_+1a    b');

此外，您可以使用正则表达式，但这需要安装一个MySql扩展。

- Артур Курицын

Answer 9

注意，像 ’ 或 » 这样的字符在 MySQL 中被视为字母。

最好使用以下内容：

IF c BETWEEN 'a' AND 'z' OR c BETWEEN 'A' AND 'Z' OR c BETWEEN '0' AND '9' OR c = '-' THEN

- Abdel

Answer 10

这可以通过我在另一个答案中发布的正则表达式替换函数来完成，并且我在这里博客中也有介绍。它可能不是最有效的解决方案，对于手头的工作可能看起来有些过度 - 但就像瑞士军刀一样，它可能会因为其他原因而派上用场。

可以在这个 Rextester 在线演示中看到其移除所有非字母数字字符的效果。 SQL (为简洁起见，不包括函数代码):

SELECT txt,
       reg_replace(txt,
                   '[^a-zA-Z0-9]+',
                   '',
                   TRUE,
                   0,
                   0
                   ) AS `reg_replaced`
FROM test;

- Steve Chambers