在MySQL中删除非字母数字字符

Question

你知道在Mysql中从varchar变量中删除（或替换）所有非字母数字字符的简单方法吗？类似于Java中的String的replaceAll（“ [^ a-zA-Z0-9]”，“ I”）（“ I”是我特殊的字符，但“”也可以）。

- Wujek Brulion

---

在将字符串传递给mysql之前，将\W替换为""。 - Amarghosh

这些字符串已经在数据库中了 :( ...而且有无数个... - Wujek Brulion

Answer 1

我将使用类似以下的方式处理每个字符串（我将每个不良字符替换为“O”）：

CREATE FUNCTION removeNonAlphaNum (p_zthes VARCHAR(255)) RETURNS VARCHAR(255)
BEGIN
DECLARE v_i INTEGER;
DECLARE v_char VARCHAR(1);
DECLARE v_res VARCHAR(255);
SET v_i := LENGTH(p_zthes);
SET v_res:=p_zthes;
WHILE v_i > 0 DO
    SET v_char := SUBSTRING(p_zthes, v_i, 1);
    IF (SELECT v_char REGEXP '[^a-zA-Z0-9]') THEN
      SET v_res := REPLACE(v_res, v_char, 'O');
    END IF;
    SET v_i := v_i - 1;
  END WHILE;
return v_res;
END 

但我以为我可以避免这样的怪物（迭代字符串中的字符并检查每个字符是否符合正则表达式... bleeeeee...）:-/ 我仍然需要测试它。

难道没有更性感的解决方案吗？

- Wujek Brulion

Answer 2

根据RegexBuddy的文档，MySQL似乎不提供这个功能（与PostgreSQL不同）：

MySQL对正则表达式的支持相当有限，但仍然非常有用。 MySQL只有一个运算符允许您使用正则表达式。 这是REGEXP运算符，它的工作方式类似于LIKE运算符，不同之处在于它使用POSIX扩展正则表达式（ERE）而不是使用_和%通配符。

- Tim Pietzcker

Answer 3

自 MySQL 8.0 起，您可以使用正则表达式从变量中删除非字母数字字符。有一个名为 REGEXP_REPLACE 的方法可供使用。

SELECT REGEXP_REPLACE(@variable, '[^0-9a-zA-Z ]', '')

or

SET @variable = REGEXP_REPLACE(@variable, '[^0-9a-zA-Z ]', '')

- michal.jakubeczy

Answer 4

这是在SQL Server中的解决方案。但是概念是这样的，我创建了一个数字表并分割了字符，然后将其与正则表达式进行匹配。

希望在MySQL中可以用稍微改变的方式呈现相同的概念，希望您能够做到。

    declare @str varchar(50)
    set @str = '1ab3^45)(*%'

    declare @NumberTable table(id int)
    insert into @NumberTable(id) values(1)
    insert into @NumberTable(id) values(2)
    insert into @NumberTable(id) values(3)
    insert into @NumberTable(id) values(4)
    insert into @NumberTable(id) values(5)
    insert into @NumberTable(id) values(6)
    insert into @NumberTable(id) values(7)
    insert into @NumberTable(id) values(8)
    insert into @NumberTable(id) values(9)
    insert into @NumberTable(id) values(10)
    insert into @NumberTable(id) values(11)
    insert into @NumberTable(id) values(12)

    select NonAlphaChars = SUBSTRING(@str,id,1) from @NumberTable
    where SUBSTRING(@str,id,1) like '%[^a-z0-9]'

非字母字符

^
)
(
*
%

- priyanka.sarkar

---

嗯，它是否比遍历字符串更好（更快）？ - Wujek Brulion

Answer 5

对于MSSQL，我已经提交了类似这样的内容：

CREATE FUNCTION removeNonAlphaNum(@p_zthes VARCHAR(255)) 
RETURNS varchar(255) 
AS
BEGIN
DECLARE @v_bad_char_index INT;
DECLARE @v_bad_char VARCHAR(1);
DECLARE @v_res VARCHAR(255);
SET @v_res = @p_zthes;
SET @v_bad_char_index = patindex('%[^a-zA-Z0-9]%', @p_zthes) 
WHILE (@v_bad_char_index > 0)
  BEGIN
    SET @v_bad_char = SUBSTRING(@p_zthes, @v_bad_char_index, 1);
    SET @v_res = REPLACE(@v_res, @v_bad_char, 'O');
    SET @v_bad_char_index = patindex('%[^a-zA-Z0-9]%', @v_res ) 
  END
return @v_res;
END 

（但我是个不太擅长 SQL 编程的程序员，所以可能存在更好的解决方案）

- Wujek Brulion