On Mon, 2006-10-07 at 10:33 -0700, Karen Hill wrote:
> Hello,
>
> How would one go about creating a US telephone type in the format of
> "(555)-555-5555" ? I am at a loss on how it could be accomplished in
> the most correct way possible while not going into the various
> different country styles e.g. +01 (555) 555-5555.
>
> Is the difficulty of creating a telephone type the reason it is not in
> postgresql already?
>
> Should the telephone type be able to do something such as:
>
> SELECT * from tableFOO where telephone.areacode = 555;
>
> Or would regex be better?
>
>
> regards,
>
Someone mentioned separating the NPA/NXX, but it is likely better
to store the phone number as formatted text.
"(123) 456-7890" -> "123-456-7890"
or if you need international/North America mixes try:
"1 (123) 456-7890" -> "1-123-456-7890"
"01 (555) 333-1212" -> "01-555-333-1212"
It is fairly simple to extract the Country Code/NPA/NXX/Line from
that format using split_part(ph,'-',#) where # is a digit from 1 to
4. It is also fairly simple to add an extension using a decimal
point, which can be extracted using split_part(ph,'.',2).
I normally determine the allowed number formats using data entry
filters in the front end, then store the data as a formatted
string :
ccc-npa-nxx-line.ext
Where ccc is the Country Code.
Depending on your needs you may want to store the raw number
and the formatted number separately.
In many jurisdictions it is now necessary to dial 10 digit
phone numbers so that should be the minimum used.
Although the NPA/NXX can be used in many cases to determine a
local, there are changes to these assignments on a regular basis
and access to that information is quite expensive. I looked
into accessing the data for a VOIP project I was working on
and was quite surprised when I discovered the access costs.
there can be other reasons why the data is unreliable as well
since many jurisdictions have legislated that phone companies
make there numbers available using LNP {Local Number Portability}
to other local phone providers. Using LNP and VOIP combinations
can allow someone to use their local phone number anywhere
in the world, just as they can with a Satellite phone.
Best of Luck