Author Topic: Standardized Phone Numbers (Read 3044 times)

dcbour · September 09, 2009, 03:30:40 am

Since I've found a bunch of different formats used for phone numbers, which of course, don't search well... I did a sql query on the backend to clean up and standardize them. It is designed only for North American calling pattern numbers. Tweak to your hearts content.

update `civicrm_phone` set phone =
concat(
mid(replace( replace(replace(replace(replace(phone,'(',''),')',''),'-',''),'.',''),' ',''),1,3)
,'-' ,
mid(replace( replace(replace(replace(replace(phone,'(',''),')',''),'-',''),'.',''),' ',''),4,3)
,'-' ,
mid(replace( replace(replace(replace(replace(phone,'(',''),')',''),'-',''),'.',''),' ',''),7,4)
,' ' ,
mid(replace( replace(replace(replace(replace(phone,'(',''),')',''),'-',''),'.',''),' ',''),11,99)
)

Anything in formats
5555551212
(555)555-1212
555.555.1212
555-555-1212

all become

555-555-1212

extensions are simply added padded by leading space - no standardization of the extension marker...

ie
(555)555-1212 x100 becomes 555-555-1212 x100
555.555.1212 ext 100 becomes 555-555-1212 ext 100

numbers with a leading 1 would need to be cleaned up first, similarly international numbers would be mangled by this.

Now, if I get a bit of time to understand the code, to see how to create a plugin to do this periodically as part of the admin functions. Anyone care to take a stab at it, be my guest as coding isn't my strong point.

hourback · January 30, 2010, 01:08:39 pm

Found this:

http://sayspy.blogspot.com/2008/12/formatting-phone-numbers-to-standard.html

The only technical recommendation I read, which came from Microsoft (. . .), suggested that -- due to the unpredictability of how phone numbers might be dialed, depending on your location -- phone numbers components (country code, area code, extension, ?) each be stored in its own field; the eventual format in which they will be used may be ambiguous so why not be flexible and store them in such a way that does not dictate any restrictions on their format.

I *would* follow that advice, except:

This is my first CiviCRM installation.
The client has been very consistent in their formatting thus far so no other normalization is required.
Seems like, if the time were to come where international standardization were to become necessary, that it would be easy enough to create some custom fields and parse out the phone numbers myself.
CiviCRM.org might end up implementing similar changes and I wouldn't want to create more work for whomever is administering CiviCRM for the client at that time, by requiring the administrator (let's call her Future Admin) to adapt the data model to CiviCRM's native implementation.

Basically, "do it like CiviCRM does it," which has been my guiding principle since undertaking this first project. :-) Hopefully it will prevent some bruising.

-Ali

demeritcowboy · August 02, 2010, 12:43:13 pm

Any thoughts on a setting where you specify the desired format(s) as a regex, and then the form doesn't pass validation if you enter a phone number not in that format?

I know some people don't like regexes but it would be mostly variations of repeating the "\d" symbol, and the regex format automatically provides for specifying multiple allowed formats in a single settings field.

CiviCRM Community Forums (archive)

News:

Author Topic: Standardized Phone Numbers (Read 3044 times)

dcbour

Standardized Phone Numbers

hourback

Re: Standardized Phone Numbers

demeritcowboy

Re: Standardized Phone Numbers