CODEWITHSUNDEEP
mysql
Shashiram Piendla
Shashiram Piendla

Reputation: 11

REGEX in mysql query

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.

Upvotes: 1

Views: 2925

Answers (6)

Nishant
Nishant

Reputation: 55876

You may use this MySQL UDF. And then simply, 

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

Upvotes: 0

Haim Evgi
Haim Evgi

Reputation: 125614

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

Upvotes: 1

Aron Rotteveel
Aron Rotteveel

Reputation: 83213

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.

Upvotes: 0

Bj&#246;rn
Bj&#246;rn

Reputation: 29411

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

Upvotes: 0

Silver Light
Silver Light

Reputation: 45962

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

Upvotes: 0

Raffael
Raffael

Reputation: 20045

You don't need RegExp for simple character replacement.

MySQL string functions

Upvotes: 0

Related Questions