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;

Somnath Muluk · Accepted Answer · 2012-03-29 18:46:37Z

1

Use this sql query:

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

58.3k39 gold badges225 silver badges230 bronze badges

answered Jul 8, 2010 at 4:41

6,7441 gold badge25 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1