Update telephone numbers

[Steve in S.F.]

I have a database in which due to past entries, some phone numbers include
the formatting, i.e. (555) 123-4567 and others used an input mask and simply
have the digits, i.e. 5551234567.

I would like to do an update query that changes only those numbers with the
formatting included to remove the parenthesis, hyphens, and spaces and just
leave the 10 digits.

Can anybody suggest how to do this in terms of what to enter for the
"criteria" line and what to enter for the "update to" line?

Thanks,

Steve Sherman

 

[Ofer Cohen]

First back up your data.

You can try an update query, using the replace function

UPDATE TableName SET TableName.FieldName=
Replace(Replace(Replace(Replace([FieldName]," ",""),"(",""),")",""),"-","")