True comments, all - and we haven't even gotten into the problem of telephone extensions. But the original question was about 10 digit numbers so I assumed vanilla US area+prefix+number. Cheers, Steve On Monday 24 February 2003 11:33 am, scott.marlowe wrote: > On Mon, 24 Feb 2003, Steve Crawford wrote: > > > > oh and what is the best datatype to use for a 10 digit phone number?. > > > > -snip- > > > > > Secondly, for a phone number, ask yourself how you're going to treat > > > it. Are you going to do a sum() across the numbers? Or maybe multiply > > > them together? > > > > > > If yes, then you should store them as some kind of numeric, int, or as > > > a float. > > > > > > If, however, the numbers are not going to be used for math but for > > > identification, then it is likely that a text / varchar type would be a > > > better choice. > > > > Don't use int: > > create table foo (ph int); > > insert into foo values (5105551212); > > ERROR: dtoi4: integer out of range > > > > Use char(10). > > Actually, I'd use text or something, because I store international > and US phone numbers. Some are easily 15 or more characters long. > > > Better yet, "properly" normalize phone numbers into area-code (char(3)), > > prefix (char(3)) and number (char(4)) fields. This way you can > > error-check your phones against the area-code table, determine > > approximate geographical areas/time-zones, flag dangerous numbers (very > > high cost off-shore versions of 900/976 numbers that look like ordinary > > phone numbers), etc. > > Yes, but then absolutely nothing but US phone numbers will fit. While > that's a great idea if all you're storing are US numbers, it doesn't fit > all models. I don't think it's possible to come up with a regex that > will qualify all the goofy phone numbers my company's database stores. > > > If you really want to you can even include a prefix table to do the same > > thing at the exchange level using NANPA data. > > We actually do something similar. On campus we have 4 digit numbers, but > we have four different prefixes depending on range. i.e. prefix 123 is > used for say 0001 through 2999, while prefix 456 is used on 3000 through > 4499, then 879 for 4500 through 7999 and so on. So we join them based on > range. Works pretty well, but it's ugly. > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster
