Replace () / . characters in phone numbers with dashes (MySQL database)
If you have a MySQL database containing phone number records in varying formats, you may want to standardize them. New records can easily be handled by checking data entry against your desired format (via JS or whatever method you like). However, existing records must be dealt with. That is where an UPDATE statement combined some handy MySQL functions can assist us:
// Replace forward slash with dash
UPDATE db_phonenumbers SET phone_field = REPLACE(phone_field, ‘/’, ‘-‘)
// Replace close parenthesis with dash
UPDATE db_phonenumbers SET phone_field = REPLACE(phone_field, ‘)’, ‘-‘)
// Remove open parenthesis
UPDATE db_phonenumbers SET phone_field = REPLACE(phone_field, ‘(‘, ”)
// Replace period with dash
UPDATE db_phonenumbers SET phone_field = REPLACE(phone_field, ‘.’, ‘-‘)
Hopefully this will be helpful to you! 🙂
More info:
http://stackoverflow.com/questions/10177208/update-a-column-value-replacing-part-of-a-string
http://www.mydigitallife.info/how-to-find-and-replace-text-in-mysql-database-using-sql/