REGEX in mysql query

Question

i have a table with address as column.

values for address is "#12-3/98 avenue street", which has numbers, special characters and alphabets.

i want to write my sql query usng regex to remove special characters from the address value

ex: "12398avenuestreet" will be the value i want after removing the special characters

thank you.

score 1 · Answer 1 · answered Feb 14 '11 at 09:42

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

score 0 · Answer 2 · answered Feb 14 '11 at 09:35

0

You don't need RegExp for simple character replacement.

MySQL string functions

answered Feb 14 '11 at 09:35

Raffael

19,547
15
82
160

score 0 · Answer 3 · answered Feb 14 '11 at 09:37

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

score 0 · Answer 4 · answered Feb 14 '11 at 09:39

0

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().

answered Feb 14 '11 at 09:39

Björn

29,019
9
65
81

could you please breif about how do i do it by creating a function – Shashiram Piendla Feb 14 '11 at 09:42
You have the link in my answer, it's the full function declaration. – Björn Feb 14 '11 at 09:43
can i use this regex "[A-Za-z 0-9]" to check for alphabets and numbers – Shashiram Piendla Feb 14 '11 at 09:48

score 0 · Answer 5 · answered Feb 14 '11 at 09:39

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.

score 0 · Answer 6 · answered Feb 14 '11 at 09:46

0

You may use this MySQL UDF. And then simply,

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

answered Feb 14 '11 at 09:46

Nishant

54,584
13
112
127

REGEX in mysql query

6 Answers6

Linked