98

Possible Duplicate:
mysql datatype for telephne number and address

Any suggestions on best practice to store telephone numbers in a DB? Consider a US phone number:

  • 555 555 1212
  • 555-555-1212
  • (555) 555 1212
  • 5555551212
  • 1-555-555-1212
  • 1 (555) 555-1212
  • and so on ...

Should I remove formatting and store only numbers? Should I just use one field -- or split them up into: country code, area code, phone number, etc.? Suggestions?

ivanleoncz
  • 5,662
  • 3
  • 49
  • 42
StackOverflowNewbie
  • 35,023
  • 98
  • 252
  • 421

11 Answers11

185
  • All as varchar (they aren't numbers but "collections of digits")
  • Country + area + number separately
  • Not all countries have area code (eg Malta where I am)
  • Some countries drop the leading zero from the area code when dialling internal (eg UK)
  • Format in the client code
gbn
  • 394,550
  • 75
  • 549
  • 647
  • 1
    We had a recent requirement similar to this. After analysis we decided to store it in 4 different varchar fields: State Code, Area Code, Number and Extension (length 5 and optional). You could include a separate field for country if you'd like. – Ashok Goli Nov 27 '11 at 12:13
  • I disagree with "they aren't numbers". It's true if think that the initial zeros are part of the number but I don't. I see that as a way of indicating on what level the number you're dialing is. 00 is global, 0 is national and no zeros is local. I don't think any leading zeros should be stored, they should be added by the application that uses the number. – Andreas Wederbrand Nov 27 '11 at 13:01
  • 10
    @Andreas Wederbrand: do you add or subtract or round or modulo phone numbers? No. – gbn Nov 27 '11 at 15:09
  • 3
    @Andreas All Phone Numbers in my mobile phone contain a + sign. A + sign is not a number. My Phone Number is not (555) 123-4567, it's +15551234567 and the phone number of my parents is not 02134 123456, it is +492134123456. Those fields are not numbers. – Michael Stum Nov 27 '11 at 15:17
  • @Michael Stum: good point. I'm the same with numbers from Malta, Switzerland and UK – gbn Nov 27 '11 at 15:57
  • An neither will you ever see any a, b or c. The + simply says "Hi!, I'll be dialing including country prefix.", 00 says the same and 0 says "I'll be dialing internally so auto add my own country prefix". I don't need the + sign in my database, and I couldn't send it to my SMS-gateway, they expect 00. In either case, I'll add that in my application. – Andreas Wederbrand Nov 27 '11 at 17:30
  • @BurhanAli, they ARE numbers. – Pacerier Oct 13 '12 at 13:04
  • 7
    @gbn That strikes me as false logic. If you choose not to perform arithmetic on a number that doesn't suddenly make it not a number. That's like saying an auto incremented surrogate primary key is not a number just because its value has no arithmetic significance, only its uniqueness. Ultimately it is just semantics--whether a given definition of "number" is useful in the situation or decision at hand. Whether or not one defines it as a "number" bears only superficially on deciding how to store it. – Chad N B Feb 05 '13 at 20:01
  • @AndreasWederbrand I know that I am way late, but I just cant believe that your position is to shore phone number parts as INTs in a database. I would beg you to reconsider your position. Using a number datatype will cause a lot of pain on developers that work with your databases. – Scott Adams May 03 '13 at 19:49
  • I have yet to find a good reason NOT to store them as number besides if you need to store the formatting in the database (and I think that is a bad idea as well). – Andreas Wederbrand May 03 '13 at 19:53
  • How do you store leading zeroes then? And then how to distinguish area codes vs country codes? You keep saying they are numbers but everyone disagrees... – gbn May 04 '13 at 08:43
  • ISO Country code for Malta is: MT/MLT with number: 356, source: http://countrycode.org/ –  Jul 27 '14 at 10:13
  • In my case, we are only storing the area code and phone number. So I made it as **bigint** and set a Primary Key on it, so there is no duplicates. Country codes are in the Country table. And profile has link to CountryID. So now I can get full phone 12223334444 (where 1 is country code, 2223334444 is phone number including area code). Then I simply add `+` in front of it, before calling through any VoIP api. I was able to do this because North American [at least CA & US] don't have their area code starts with 0 – SKCS Kamal Dec 20 '17 at 14:38
  • 2
    My use case is with using Twilio. They suggest using a *standard* way of storing called the E.164 standard so that all numbers are in the same formatting. – user3245268 Mar 03 '19 at 23:48
  • As stated in the previous comment, if you use E.164 you can store them as 8-byte integers in the database. You will likely save a few bytes per row, and in addition will not have any formatting issues since the client will be forced to insert a valid number. I can't believe how many answers for questions like this suggest using numbers is wrong when it is clearly the best option. – Lucas Nov 24 '20 at 11:12
  • Best to store the phone numbers as numbers/integers in MSISDN format https://en.wikipedia.org/wiki/MSISDN (it saves you storage space and is more efficient too). When you add the + and 0 or 00, those are phone codes as described by some in previous comments. I started out thinking they should be string, but after some research, phone numbers are numbers. How to retrieve or display them is up to you. – Akinjiola Toni Mar 11 '21 at 06:09
28

You should never store values with format. Formatting should be done in the view depending on user preferences.

Searching for phone nunbers with mixed formatting is near impossible.

For this case I would split into fields and store as integer. Numbers are faster than texts and splitting them and putting index on them makes all kind of queries ran fast.

Leading 0 could be a problem but probably not. In Sweden all area codes start with 0 and that is removed if also a country code is dialed. But the 0 isn't really a part of the number, it's a indicator used to tell that I'm adding an area code. Same for country code, you add 00 to say that you use a county code.

Leading 0 shouldn't be stored, they should be added when needed. Say you store 00 in the database and you use a server that only works with + they you have to replace 00 with + for that application.

So, store numbers as numbers.

Andreas Wederbrand
  • 33,680
  • 10
  • 58
  • 75
  • It's not always about speed; it must be convenient to use also. The admin could benefit from using varchar instead since we don't need to do any arithmetic on the numbers. But of course, if the performance is crucial.. – Marcus Nov 27 '11 at 10:00
  • 2
    Of course it depends on the application but numbers should be stored as numbers :) No gain in keeping it as varchar. – Andreas Wederbrand Nov 27 '11 at 12:27
  • 1
    @AndreasWederbrand: Telephone numbers are not numbers really. (unless you are a Pythagoras fan, then everything is a number :).You don't need to add, sustract, multiply or find the opposite of phone "numbers". I agree on the rest (about leading 0s, etc.) – ypercubeᵀᴹ Dec 01 '11 at 09:16
  • 4
    that might be true, but they are a very well defined subset of all characters, namely those that correspond to the 10 numbers. Booleans arent 0 or 1 either, but we normally store them as tinyint(1) anyway. Either way, I feel I've given my opinion and OP has probably enough to make a decision :) – Andreas Wederbrand Dec 01 '11 at 09:37
  • @AndreasWederbrand I don't store Booleans as tinyint. I use bit. – Scott Adams May 03 '13 at 19:57
  • 1
    That is possible but the datatype Boolean is a synonym for tinyint(1) – Andreas Wederbrand May 03 '13 at 20:13
  • Phone numbers are not numeric values. They don't exist on a number line or anything. The base 2 representation of 1234567890 is not semantically equivalent (although it might be a compact encoding). They are a representation of a sequence of digits that you press. So they are a string of digits. – nafg Feb 01 '18 at 21:37
  • Ditto on not being numbers. It is tempting to treat a string of numbers as a number, but leading 0's, as you stated, defeats this. The varchar is more resilient. – rfportilla Mar 07 '18 at 15:12
  • "Numbers are faster than texts". That is correct and well mentioned, indeed. – ivanleoncz Mar 14 '19 at 18:22
  • 1
    "Numbers are faster than text" ... Not really. You have to stringify them at some point anyways, so you're just moving the goalpost. You can't search for a partial number without stringifying.. And in Big O, it's exactly the same. – wizulus Jan 14 '20 at 21:01
  • In some countries number part (country+area+number) could start with zero. If you store this parts as numbers you will lose that information. – Nuri Tasdemir Feb 14 '21 at 22:56
  • What country would that be? 00 means "next up is a country code" and 0 means "next is the area part". That is a global standard as far as I know. – Andreas Wederbrand Feb 15 '21 at 15:14
15

I suggest storing the numbers in a varchar without formatting. Then you can just reformat the numbers on the client side appropriately. Some cultures prefer to have phone numbers written differently; in France, they write phone numbers like 01-22-33-44-55.

You might also consider storing another field for the country that the phone number is for, because this can be difficult to figure out based on the number you are looking at. The UK uses 11 digit long numbers, some African countries use 7 digit long numbers.

That said, I used to work for a UK phone company, and we stored phone numbers in our database based on if they were UK or international. So, a UK phone number would be 02081234123 and an international one would be 001800300300.

Steve Rukuts
  • 8,609
  • 3
  • 43
  • 67
  • CHAR(255) would be better, right? Would you recommend putting the entire number in one field? Or split them up? If split up, how do you suggest I do that? – StackOverflowNewbie Nov 27 '11 at 09:44
  • 2
    Well, we used VARCHAR(20). We stored numbers for every country in the world and 20 was the standard in the company. 255 seems far larger than I would recommend, especially for a static field format. – Steve Rukuts Nov 27 '11 at 09:45
  • Addendum to the above: That is, if you're not storing extension or other extended information. But if you're storing extension info, you should probably put that in another field anyway. – Steve Rukuts Nov 27 '11 at 09:47
  • The drawback with this method is that it will be terribly inefficient if you need to search in this column; since you'll have to convert them before you can compare. – Marcus Nov 27 '11 at 09:49
  • So, split up the phone number into segments for country code, area code, actual number, extension? – StackOverflowNewbie Nov 27 '11 at 09:55
  • Actually, this http://electronics.howstuffworks.com/question659.htm breaks up "actual number" into 2 parts: prefix and line number. – StackOverflowNewbie Nov 27 '11 at 10:00
  • @StackOverflowNewbie: that link is too localised – gbn Nov 27 '11 at 10:32
  • I'm not actually sure if I'd recommend storing the area code separately unless you actually need to match on this for something. Also, bear in mind that area codes aren't necessarily correct; it depends on your country. In the UK, I can very easily give myself a Welsh phone number, even though I'm in London. – Steve Rukuts Nov 27 '11 at 10:50
  • @Marcus: Good point. Perhaps it might be worth storing the entire phone number including country prefix, but when the user inputs the data, have them enter the country code separately. This would allow you to easily determine what part of the phone number is the country code. – Steve Rukuts Nov 27 '11 at 10:57
  • If you store country prefix together, it'll be hard to parse it out later. – StackOverflowNewbie Nov 29 '11 at 05:14
11

varchar, Don't store separating characters you may want to format the phone numbers differently for different uses. so store (619) 123-4567 as 6191234567 I work with phone directory data and have found this to be the best practice.

SurferJoe
  • 935
  • 1
  • 8
  • 13
4

I would suggest a varchar for the phone number (since phone numbers are known to have leading 0s which are important to keep) and having the phone number in two fields:

Country Code and phone number i.e. for 004477789787 you could store CountryCode=44 and phone number=77789787

however it could be very application specific. If for example you will only store US numbers and want to keep the capability of quickly performing queries like "Get all the numbers from a specific area" then you can further split the phone number field (and drop the country code field as it would be redundant)

I don't think there is a general right and wrong way to do this. It really depends on the demands.

apokryfos
  • 30,388
  • 6
  • 55
  • 83
2

Suggest that you store the number as an extended alphanumeric made up of characters that you wish to accept and store it in a varchar(32) or something like that. Strip out all the spaces , dashes, etc. Put the FORMATTING of the phone number into a separate field (possibly gleaned from the locale preferences) If you wish to support extensions, you should add them in a separate field;

Ahmed Masud
  • 19,014
  • 3
  • 28
  • 53
1

I would recommend storing these as numbers in columns of type varchar - one column per "field" (like contry code etc.).

The format should be applied when you interact with a user... that makes it easier to account for format changes for example and will help esp. when your application goes international...

Yahia
  • 67,016
  • 7
  • 102
  • 131
1

Form my point of view, below is my suggestions:

  1. Store phone number into a single field as varchar and if you need to split then after retrieve split accordingly.
  2. If you store as number then preceding 0 will truncate, so always store as varchar
  3. Validate users phone number before inserting into your table.
Elias Hossain
  • 4,130
  • 1
  • 17
  • 32
0

I would definitely split them. It would be easy to sort the numbers by area code and contry code. But even if you're not going to split, just insert the numbers into the DB in one certain format. e.g. 1-555-555-1212 Your client side will be thankfull for not making it reformat your numbers.

mintobit
  • 2,343
  • 2
  • 12
  • 15
-2

You can use varchar for storing phone numbers, so you need not remove the formatting

Sudhir Bastakoti
  • 94,682
  • 14
  • 145
  • 149
-4

I would say store them as an big integer, as a phone number itself is just a number. This also gives you more flexibility in how you present your phone numbers later, depending on what situation you are in.

Alfo
  • 4,413
  • 9
  • 35
  • 50
  • 5
    A phone number is not really a number. For one, it may have leading zeros. – charstar Nov 27 '11 at 09:50
  • 2
    a phone number is a dial code, and strictly speaking you can actually dial '*' and '#' for north american tonal system... and there are others for various areas. Also, there are other programatic things you can do, for example you can program pauses during a dial. Or you could actually put a '+' to indicate international dialling code for the local area. – Ahmed Masud Nov 27 '11 at 12:17
  • Why then did the user above get several plus points for their answer which was effectively the same as mine? He explains more, but he comes to the same conclusion as me. – Alfo Nov 30 '11 at 13:48
  • The previous answer recommending numbers explained why in detail, with separate fields for each component of the phone number. Your answer was a single large integer and doesn't explain WHY it is more flexible. – Chris Hansen Sep 18 '20 at 17:00