I have thousands of fields in a datbase that I imported from a text file, the only problem was that the old system didn't format phone number correctly.

(xxx) 555-1212
516-555-1212

and so on

I want to remove all non-numbers for them field, is there a query I can use to do this faster than going through each row and changing it myself

UPDATE locations SET location_phone= '6315874729' WHERE location_id=1805

would be the query to change the number.

Thanks

The only way I can think of is to repeatedly remove unwanted characters in the field:


update locations set location_phone=replace(location_phone,'-','');
update locations set location_phone=replace(location_phone,'(','');
update locations set location_phone=replace(location_phone,')','');


...and so on. You can remove more than one character per update using nested replace() calls, but you get the idea.

Thank you,

That helped alot, I didn't have to go through and change every record.