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

小开

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

小开

从表演的角度来看, (假设你读的比写的多)

我认为最好的方法是预先计算并存储一个精简版本的列, 这样可以减少变形。

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

小开

我已经编写了这个 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 扩展。

小开

注意，像’或者“这样的字符在 MySQL 中被认为是 alpha 字符。最好使用这样的词:

如果 c 在‘ a’和‘ z’之间或者 c 在‘ a’和‘ Z’之间或者 c 在‘0’和 ’9’或 c =’-’然后

小开

我能找到(并使用)的最快方法是使用 Convert () .

使用 USING 从 Doc. CONVERT ()转换不同字符集之间的数据。

例如:

convert(string USING ascii)

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

注意，CONVERT()的使用表格是从 4.1.0开始提供的。

小开

与其他人相比，这可能是一个愚蠢的建议:

if(!preg_match("/^[a-zA-Z0-9]$/",$string)){
$sortedString=preg_replace("/^[a-zA-Z0-9]+$/","",$string);
}

小开

到目前为止，唯一一个不像其他答案那么复杂的替代方法是确定该列的全部特殊字符，即该列目前使用的所有特殊字符，然后对所有这些字符进行顺序替换，例如。

update pages set slug = lower(replace(replace(replace(replace(name, ' ', ''), '-', ''), '.', ''), '&', '')); # replacing just space, -, ., & only

.

对于已知的数据集，建议使用只有，否则一些特殊字符通过一个黑名单方法而不是白名单方法。

显然，最简单的方法是预先验证 sql 之外的数据，因为缺乏健壮的内置白名单(例如，通过 regex 替换)。

小开

使用 MySQL 8.0或更高版本

感谢 michal.jakubeczy 在下面的回答，现在 MySQL 支持正则表达式:

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

使用 MySQL 5.7或更低版本

这里不支持正则表达式，我必须创建我自己的名为 alpha 的函数，它为我删除了字符:

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)

万岁！

小开

我需要在一个过程中只获取字符串的字母字符，并且做到了:

SET @source = "whatever you want";
SET @target = '';
SET @i = 1;
SET @len = LENGTH(@source);
WHILE @i <= @len DO
SET @char = SUBSTRING(@source, @i, 1);
IF ((ORD(@char) >= 65 && ORD(@char) <= 90) || (ORD(@char) >= 97 && ORD(@char) <= 122)) THEN
SET @target = CONCAT(@target, @char);
END IF;
SET @i = @i + 1;
END WHILE;

小开

我尝试了一些解决方案，但最终使用了 replace。我的数据集是零件编号，我相当清楚要期待什么。但是为了理智起见，我用 PHP 构建了一个长长的查询:

$dirty = array(' ', '-', '.', ',', ':', '?', '/', '!', '&', '@');
$query = 'part_no';
foreach ($dirty as $dirt) {
$query = "replace($query,'$dirt','')";
}
echo $query;

这输出了一些我曾经头疼的东西:

replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(part_no,' ',''),'-',''),'.',''),',',''),':',''),'?',''),'/',''),'!',''),'&',''),'@','')

小开

我也遇到过类似的问题，在我们的数据库中试图匹配稍有不同的姓氏。例如，有时人们输入同一个人的名字作为“麦当劳”，也作为“麦当劳”，或“圣约翰”和“圣约翰”。

我没有尝试转换 Mysql 数据，而是通过创建一个函数(用 PHP 编写)来解决这个问题，该函数将接受一个字符串并创建一个只包含 alpha 的正则表达式:

function alpha_only_regex($str) {
$alpha_only = str_split(preg_replace('/[^A-Z]/i', '', $str));
return '^[^a-zA-Z]*'.implode('[^a-zA-Z]*', $alpha_only).'[^a-zA-Z]*$';
}

现在我可以通过这样的查询来搜索数据库:

$lastname_regex = alpha_only_regex($lastname);
$query = "SELECT * FROM my_table WHERE lastname REGEXP '$lastname_regex';

小开

基于答案来自 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 ;

小开

这可以通过我在另一个答案中发布的正则表达式替换函数来实现，并且我已经在关于给你的博客中发表了相关文章。这可能不是最有效的解决方案，而且对于手头的工作来说可能看起来过于杀伤力——但是就像瑞士军刀一样，它可能因为其他原因而派上用场。

可以看到它正在移除这个 Rextester 在线演示中的所有非字母数字字符。

SQL (不包括简洁的函数代码):

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

小开

拉丁字母和西里尔字母的直接和战斗的解决方案:

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-привет ""]')

小开

如果您正在使用 php，那么... ..。

try{
$con = new PDO ("mysql:host=localhost;dbname=dbasename","root","");
}
catch(PDOException $e){
echo "error".$e-getMessage();
}


$select = $con->prepare("SELECT * FROM table");
$select->setFetchMode(PDO::FETCH_ASSOC);
$select->execute();


while($data=$select->fetch()){


$id = $data['id'];
$column = $data['column'];
$column = preg_replace("/[^a-zA-Z0-9]+/", " ", $column); //remove all special characters


$update = $con->prepare("UPDATE table SET column=:column WHERE id='$id'");
$update->bindParam(':column', $column );
$update->execute();


// echo $column."<br>";
}

小开

Alpha 函数(自我回答)有一个错误，但我不知道为什么。对于文本“ cas synt ls 75W1401L”返回“ cassyntls75W1401”，“ L”从结尾处有些缺失。

现在我用了

delimiter //
DROP FUNCTION IF EXISTS alphanum //
CREATE FUNCTION alphanum(prm_strInput varchar(255))
RETURNS VARCHAR(255)
DETERMINISTIC
BEGIN
DECLARE i INT DEFAULT 1;
DECLARE v_char VARCHAR(1);
DECLARE v_parseStr VARCHAR(255) DEFAULT ' ';
WHILE (i <= LENGTH(prm_strInput) )  DO
SET v_char = SUBSTR(prm_strInput,i,1);
IF v_char REGEXP  '^[A-Za-z0-9]+$' THEN
SET v_parseStr = CONCAT(v_parseStr,v_char);
END IF;
SET i = i + 1;
END WHILE;
RETURN trim(v_parseStr);
END
//

(谷歌搜索)

小开

需要 替换非字母数字字符而不是删除非字母数字字符，所以我已经创建了这个基于瑞安希灵顿的字母。适用于长度不超过255个字符的字符串

DROP FUNCTION IF EXISTS alphanumreplace;
DELIMITER |
CREATE FUNCTION alphanumreplace( str CHAR(255), d CHAR(32) ) RETURNS CHAR(255)
BEGIN
DECLARE i, len SMALLINT DEFAULT 1;
DECLARE ret CHAR(32) 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);
ELSE SET ret=CONCAT(ret,d);
END IF;
SET i = i + 1;
END;
UNTIL i > len END REPEAT;
RETURN ret;
END |
DELIMITER ;

例如:

select 'hello world!',alphanum('hello world!'),alphanumreplace('hello world!','-');
+--------------+--------------------------+-------------------------------------+
| hello world! | alphanum('hello world!') | alphanumreplace('hello world!','-') |
+--------------+--------------------------+-------------------------------------+
| hello world! | helloworld               | hello-world-                        |
+--------------+--------------------------+-------------------------------------+

如果你想要的话，你需要单独添加 alpha 函数，我这里有一个例子。

小开

因为 MySQL 8.0可以使用正则表达式从字符串中删除非字母数字字符

下面是删除非字母数字字符的代码:

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