What's the longest possible worldwide phone number I should consider in SQL varchar(length)
for phone.
considerations:
Consider that in my particular case now, I don't need cards etc. number begins with country code and ends with the extension, no Fax/Phone etc. comments, nor calling card stuff needed.
This question is related to
sql-server
validation
database-design
data-modeling
phone-number
As for "phone numbers" you should really consider the difference between a "subscriber number" and a "dialling number" and the possible formatting options of them.
A subscriber number is generally defined in the national numbering plans. The question itself shows a relation to a national view by mentioning "area code" which a lot of nations don't have. ITU has assembled an overview of the world's numbering plans publishing recommendation E.164 where the national number was found to have a maximum of 12 digits. With international direct distance calling (DDD) defined by a country code of 1 to 3 digits they added that up to 15 digits ... without formatting.
The dialling number is a different thing as there are network elements that can interpret exta values in a phone number. You may think of an answering machine and a number code that sets the call diversion parameters. As it may contain another subscriber number it must be obviously longer than its base value. RFC 4715 has set aside 20 bcd-encoded bytes for "subaddressing".
If you turn to the technical limitation then it gets even more as the subscriber number has a technical limit in the 10 bcd-encoded bytes in the 3GPP standards (like GSM) and ISDN standards (like DSS1). They have a seperate TON/NPI byte for the prefix (type of number / number plan indicator) which E.164 recommends to be written with a "+" but many number plans define it with up to 4 numbers to be dialled.
So if you want to be future proof (and many software systems run unexpectingly for a few decades) you would need to consider 24 digits for a subscriber number and 64 digits for a dialling number as the limit ... without formatting. Adding formatting may add roughly an extra character for every digit. So as a final thought it may not be a good idea to limit the phone number in the database in any way and leave shorter limits to the UX designers.
It's a bit worse, I use a calling card for international calls, so its local number in the US + account# (6 digits) + pin (4 digits) + "pause" + what you described above.
I suspect there might be other cases
In the GSM specification 3GPP TS 11.11, there are 10 bytes set aside in the MSISDN EF (6F40) for 'dialing number'. Since this is the GSM representation of a phone number, and it's usage is nibble swapped, (and there is always the possibility of parentheses) 22 characters of data should be plenty.
In my experience, there is only one instance of open/close parentheses, that is my reasoning for the above.
Assuming you don't store things like the '+', '()', '-', spaces and what-have-yous (and why would you, they are presentational concerns which would vary based on local customs and the network distributions anyways), the ITU-T recommendation E.164 for the international telephone network (which most national networks are connected via) specifies that the entire number (including country code, but not including prefixes such as the international calling prefix necessary for dialling out, which varies from country to country, nor including suffixes, such as PBX extension numbers) be at most 15 characters.
Call prefixes depend on the caller, not the callee, and thus shouldn't (in many circumstances) be stored with a phone number. If the database stores data for a personal address book (in which case storing the international call prefix makes sense), the longest international prefixes you'd have to deal with (according to Wikipedia) are currently 5 digits, in Finland.
As for suffixes, some PBXs support up to 11 digit extensions (again, according to Wikipedia). Since PBX extension numbers are part of a different dialing plan (PBXs are separate from phone companies' exchanges), extension numbers need to be distinguishable from phone numbers, either with a separator character or by storing them in a different column.
Source: Stackoverflow.com