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/
- phone number with dashes sql (2)
- remove dashes from phone number in sql (2)
- how to remove parentheses and dash from phone number in sql (1)
- how to remove dash in phone number sql (1)
- how to remove - from phone number in sql using replace (1)
- how to remove () and dash from phone number (1)
- how to format a phone number in mysql using . instead of - (1)
- how to add a dash in a phone number using find and replace (1)
- update phone character (1)
- format phone number for search dash mysql (1)