REGEX in mysql query

maybe this function help you

CREATE FUNCTION strip_non_alpha(
_dirty_string varchar(40)
)
RETURNS varchar(40)
BEGIN
DECLARE _length int;
DECLARE _position int;
DECLARE _current_char varchar(1);
DECLARE _clean_string varchar(40);

SET _clean_string = '';
SET _length = LENGTH(_dirty_string);
SET _position = 1;
WHILE _position <= _length DO
SET _current_char = SUBSTRING(_dirty_string, _position, 1);

IF _current_char REGEXP '[A-Za-z0-9]' THEN
SET _clean_string = CONCAT(_clean_string, _current_char);
END IF;

SET _position = _position + 1;
END WHILE;

RETURN CONCAT('', _clean_string);
END;

so you need to call this like

update mytable set address = strip_non_alpha(address);

You don't need RegExp for simple character replacement.

MySQL string functions

Unfortunately, MySQL regular expressions are "match only", you cannot do a replace in your query. This leaves you with only something like this (witch is very-very stupid):

SELECT REPLACE(REPLACE(address, '?', ''), '#', '') -- and many many other nested replaces
FROM table

Or put this logic inside your application (the best option here).

MySQL regular expressions is only for pattern matching and not replacing, so your best bet is to create a function or a repetative use of Replace().

As far as I know, it is not possible to replace via MySQL regex, since these functions are only used for matching.

Alternatively, you can use MySQL Replace for this:

SELECT REPLACE(REPLACE(REPLACE(REPLACE(address, '#', ''), '-', ''), '/', ''), ' ', '') FROM table;

Which will remove #, -, / and spaces and result in the string you want.

You may use this MySQL UDF. And then simply,

update my_table set my_column = PREG_REPLACE('/[^A-Za-z0-9]/' , '' , my_column);