Best way to store/search phone numbers?

My 2 cents: Concatenating data is easier than
extracting data from a larger element.

I'd use different fields (country code, area code,
phone number, extension, etc.) and instruct the users
not to use punctuation. The data will (hopefully) be
a little cleaner; and easier to use. Then, if you
want it all in one field, use an update query to
concatenate the values with formatting characters into
a separate field for easy reporting/printing.

Best of luck,

Andrew

--- Raise Exception <raiseexception@yahoo.com> wrote:

I am doing a standard company info database, with
phone numbers, and I
was wondering if there was a general concensus about
the best way to
store phone numbers. My problem is multiple users
will be entering in
data via a web browser (PHP backend), and the
formatting may not be
the same in all cases. I'm not sure if I should
store some sort of
standard formatting in a plain text field (e.g.,
"(555) 555-5555") or
strip out all the punctuation characters. There is
also the option of
storing the parts in different columns, but that
seems a bit overkill
(but would make searching possibly faster).

---------------------------(end of
broadcast)---------------------------
TIP 2: you can get off all lists at once with the
unregister command
(send "unregister YourEmailAddressHere" to

majordomo@postgresql.org)

__________________________________________________
Do You Yahoo!?
Make a great connection at Yahoo! Personals.
http://personals.yahoo.com

Consistently formatted data is easier to work with, so
I suggest storing the phone numbers without any
non-numeric characters. This would require writing
some application code to extract the numeric
characters, either within the database using a
trigger, or within the client application.

Arguments about whether to store data as a single
string/number or whether to break it up could go
either way. Probably you would want to at least have
a separate field for extension numbers, and if
international numbers are likely to be stored, for
country codes as well.

--- Andrew Gould <andrewgould@yahoo.com> wrote:

My 2 cents: Concatenating data is easier than
extracting data from a larger element.

I'd use different fields (country code, area code,
phone number, extension, etc.) and instruct the
users
not to use punctuation. The data will (hopefully)
be
a little cleaner; and easier to use. Then, if you
want it all in one field, use an update query to
concatenate the values with formatting characters
into
a separate field for easy reporting/printing.

Best of luck,

Andrew

--- Raise Exception <raiseexception@yahoo.com>
wrote:

I am doing a standard company info database, with
phone numbers, and I
was wondering if there was a general concensus

about

the best way to
store phone numbers. My problem is multiple users
will be entering in
data via a web browser (PHP backend), and the
formatting may not be
the same in all cases. I'm not sure if I should
store some sort of
standard formatting in a plain text field (e.g.,
"(555) 555-5555") or
strip out all the punctuation characters. There

is

also the option of
storing the parts in different columns, but that
seems a bit overkill
(but would make searching possibly faster).

---------------------------(end of
broadcast)---------------------------
TIP 2: you can get off all lists at once with the
unregister command
(send "unregister YourEmailAddressHere" to

majordomo@postgresql.org)

__________________________________________________
Do You Yahoo!?
Make a great connection at Yahoo! Personals.
http://personals.yahoo.com

---------------------------(end of
broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

__________________________________________________
Do You Yahoo!?
Find a job, post your resume.
http://careers.yahoo.com

Andrew Gould writes:

I'd use different fields (country code, area code,
phone number, extension, etc.) and instruct the users
not to use punctuation.

This will break down once someone from a different area/country tries to
enter his phone number.

You think it won't happen? I have tried to do business with quite a few
places that didn't think my business would happen. (I didn't actually end
up happening, too.)

OTOH, I once did work for an ISP that had a very specific interest in the
breakdown of phone numbers by area code, exchange, and such. I used a
text field with a couple of functional indexes, and it worked well for us.

--
Peter Eisentraut peter_e@gmx.net http://funkturm.homeip.net/~peter