MySQL - convert phone number data?

Question

I have a MySQL InnoDB database.

I have a 'phone_number' field.

My data is dirtly in the sense that sometimes my data is:

(111) 222-3333
111-222-3333
111.222.3333
(111) 222 3333

How can I strip all spaces and parenthesis from my 'phone_number' field to only store my phone_number in the following format '1112223333'?

What would the SQL be to UPDATE my 'phone_number' field to have all data in the format '1112223333'?

For example, pseudo code:

UPDATE phone_number = STRIP_SPACES_AND_PARATENSIS(phone_number) FROM homes;

Answer 1

See http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=103752 .

Answer 2

update homes set phone_number=replace(replace(replace(replace(replace(phone_number,'-',''),'(',''),')',''),'.', ''), ' ', '');