如何从MySQL中的字符串中删除所有非字母数字字符?
问题描述:
我正在研究一个比较字符串的例程,但是为了提高效率,我需要删除所有不是字母或数字的字符.
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 ?
答
这些答案中的任何一个都不适合我.我必须创建自己的函数alphanum来为我剥离字符:
None of these answers worked for me. 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)
万岁!