Removing non-alphaNumerics in MySQL

Do you know any easy way to remove (or replace) all 开发者_运维技巧non alphanumeric characters from varchar variable in Mysql?

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

It appears that MySQL doesn't provide this functionality (unlike PostgreSQL), according to the docs of RegexBuddy:

MySQL's support for regular expressions is rather limited, but still very useful. MySQL only has one operator that allows you to work with regular expressions. This is the REGEXP operator, which works just like the LIKE operator, except that instead of using the _ and % wildcards, it uses a POSIX Extended Regular Expression (ERE).

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 

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?

Since MySQL 8.0 you can use regular expression to remove non alphanumeric characters from a variable. There is method REGEXP_REPLACE

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

or

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

this is the solution in sql server. But the concept goes like I have created a number table and splitted the charecters and then matching those against the regular expression.

Hope the same concept can be portrayed in mysql with a bit change and that hopefully u can do.

    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]'

NonAlphaChars

^
)
(
*
%

For MSSQL I have committed something like this:

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 

(but I am poor SQL programmer, so there probably exist some nicer solutions)