在MySQL中删除非alphaNumerics [英] Removing non-alphaNumerics in MySQL

问题描述

您知道从MySQL的varchar变量中删除(或替换)所有非字母数字字符的简便方法吗?

Do you know any easy way to remove (or replace) all non alphanumeric characters from varchar variable in Mysql?

类似于Java中String的replaceAll("[^ a-zA-Z0-9]"，"I")("I"是我的特殊字符，但"也很好)

something like String's replaceAll("[^a-zA-Z0-9]", "I") in Java ('I' is my special character but "" would be also good )

推荐答案

我将对每个字符串使用类似的东西(我将每个坏字符替换为'O'):

I am going to use somethin like this for each string (I replace each bad char with '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 ...检查每个字符):-/我仍然需要对其进行测试.

but I thought I could avoid such monster (iterating over chars in string and checking each against regex... bleeeeee...) :-/ I still need to test it.

没有其他性感的解决方案了吗?

Aren't there any more sexy solutions?

这篇关于在MySQL中删除非alphaNumerics的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！