Removing non-alpha numbers in MySQL

Question

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

something like String replaceAll ("[^ a-zA-Z0-9]", "I") in Java ("I" is my special character, but "" will be fine too)

Answer 1

MySQL doesn't seem to provide this functionality (unlike PostgreSQL), according to the RegexBuddy docs :

MySQL support for regular expressions is quite limited, but still very useful. MySQL has only one statement that allows you to work with regular expressions. This is a REGEXP statement that works the same as a LIKE statement, except that instead of using the wildcards _ and%, it uses the extended POSIX regular expression (ERE).

Answer 2

I am going to use something similar for each line (I will replace every 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 a monster (iterating through lines in lines and checking each of the regular expressions ... bleeeeee ...): - / I still need to test it.

Are there any more sexual solutions?

Answer 3

sql-. , charecters, .

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

NonAlphaChars

^
)
(
*
%

Answer 4

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, , , )

Answer 5

MySQL 8.0, - . REGEXP_REPLACE

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

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