259

What's the longest possible worldwide phone number I should consider in SQL varchar(length) for phone.

considerations:

  • + for country code
  • () for area code
  • x + 6 numbers for Extension extension (so make it 8 {space})
  • spaces between groups (i.e. in American phones +x xxx xxx xxxx = 3 spaces)
  • here is where I need your help, I want it to be worldwide

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.

danwellman
  • 9,068
  • 8
  • 60
  • 88
Shimmy Weitzhandler
  • 101,809
  • 122
  • 424
  • 632

6 Answers6

213

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.

outis
  • 75,655
  • 22
  • 151
  • 221
Matt Enright
  • 7,245
  • 4
  • 33
  • 32
  • 5
    If you are not storing the format characters (like '+', '(', ')', '-', and ' ') and you are storing numbers from different nations, you may want to add a column to indicate the format type of the number for when the number is displayed. – Trisped Jan 03 '13 at 19:23
  • 63
    Bottom line: `15` chars. If storing prefix and suffix, the bottom line is: 5+15+11=`31`. – AlikElzin-kilaka Sep 03 '14 at 07:09
  • 6
    '+' is not presentational https://en.wikipedia.org/wiki/List_of_international_call_prefixes – qubodup Mar 17 '18 at 23:10
  • So what you need to store a phone-number is decimal(31,0). Maximum-length of decimal being 38. – Stefan Steiger Jan 14 '21 at 12:32
100

Well considering there's no overhead difference between a varchar(30) and a varchar(100) if you're only storing 20 characters in each, err on the side of caution and just make it 50.

cletus
  • 616,129
  • 168
  • 910
  • 942
  • 28
    Just for the knowledge: so when IS there any overhead? please include a source in your answer, so we can go ahead and learn the basics from it. – Shimmy Weitzhandler Jul 20 '10 at 18:20
  • 7
    I know that should be the case, but it's not always. In MySQL (for example) the full length is used for sorting. It's best to apply at least some minimal effort. – Morgan Tocker Jul 20 '10 at 18:23
  • 2
    When using a CHAR for example. SQL Server documentation (to and read it yourself) shows exactly that unused size in varchar is really not used, i.e. the overhead of unused chars is 0. – TomTom Jul 20 '10 at 18:23
  • 17
    There's no **storage size** difference between the two column sizes. There very likely is an overhead, significant or otherwise, depending on your particular database. SQL Server, for example, loses a lot of ability to predict data page sizes and optimize accesses and alignment thusly. As always, test. – Matt Enright Aug 10 '11 at 22:09
  • 19
    Premature optimization is the root of all evil. – Harindaka Sep 11 '13 at 09:47
  • 96
    Platitudinous generalizations are even worse. Designing a system with optimization in mind is **never** _evil_ _per_ _se_ - optimization becomes evil when one devotes an inordinate amount of time to unnecessary, unnoticeable and minor efficiencies. – jbowman Dec 02 '15 at 21:47
  • When allocating memory for sort operations SQL Server assumes `varchar` columns will, on average, require memory for half their declared max length. So `varchar(30)` would be reasonable if expecting an average of around 15 – Martin Smith Aug 23 '20 at 12:01
  • Postgres would be an example of a DB where it doesn't really matter. Postgres basically treats varchar as text, with the one different that it will check the input for max length of the varchar. – JCP Nov 27 '21 at 01:06
18

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.

KevinDTimm
  • 14,226
  • 3
  • 42
  • 60
11

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

webclimber
  • 2,630
  • 4
  • 26
  • 36
  • 10
    Calling card redial should not be in the database, though - this is the part that gets added when dialling according to dial rules. Stored numbers should be in ISO form, without any dial related information. – TomTom Jul 20 '10 at 18:24
5

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.

Guido U. Draheim
  • 3,038
  • 1
  • 20
  • 19
1

Digits range for all countries 4 - 13 https://en.wikipedia.org/wiki/List_of_mobile_telephone_prefixes_by_country

K D
  • 205
  • 3
  • 10