-1

I have made an Extension column in my table which is of type integer. But it ignores the 0 part of the number if it starts with it. I could change the datatype to Varchar if the circumstances want me to. But before making this change I want to confirm if the Extension could starts with 0 in any organization (Particularly in the United States and Canada).

For Example Suppose, I have an extension column in my Table. So what I have done is int ext.....
Should I change my datatype to varchar or should I assume that the Extension could never starts with 0. I am in dilemma, please give me some advice.

CodeConstruct
  • 290
  • 3
  • 17
  • dont know how it is in america, but here in europe phone are often preceeded by a + For example the phone number is 0123456 in Belgium and you are in another country than you have to call +32123456 where +32 means Belgium and because you added a country the ZERO must be omitted – GuidoG Jul 24 '17 at 10:55
  • 2
    You should only use numerical data types for things that are actual numbers. A phone number, though it contains the word "number" is not actually a number in the "can be calculated with" sense and you should thus use a string type instead. It's more of an identifier than a number. – Lasse V. Karlsen Jul 24 '17 at 11:35
  • Ask your client, or your product person, or your subject matter expert. – AakashM Jul 24 '17 at 11:36
  • I'm voting to close this question as off-topic because this isn't a software development question, it's a question about the nature of some phone numbers – AakashM Jul 24 '17 at 11:37

2 Answers2

1

I suggest switching to a varchar for several reasons:

1) a phone number is essentially a string of arbitary characters, it is not meant to be a number which can be summed or averaged etc. Setting to varchar will mean that any tools further down the line (e.g. SSAS, data viz tools) won't try and implicitly create a numeric measure from the field.

2) you can never assume what people will have put into your data source. So better to be able to accept a 0 if someone has entered one and then deal with it afterwards, rather than your application giving an error.

3) you can always check if you've got a 0 at the start of a string and then remove it if needed e.g. using charindex and substring/right etc.

4) you can allow for "+" and other characters in phone numbers as needed

Helen Gore
  • 81
  • 3
1

Dealing with phone numbers is much harder than you might expect. Read the answers and comments on this question, and have a look at the Google phone number library for a sense of how hard.

Extensions are typically handled by an inhouse PBX such as Asterisk; those systems are free to implement whatever format for extension numbers they like (as long as they are supported by the regular phone system). This includes starting with leading zeroes.

I suggest you store this data not as an integer - as @https://stackoverflow.com/users/267/lasse-v-karlsen suggests, it's not a number in the mathematical sense, but a series of symbols which happen to include numbers.

Neville Kuyt
  • 29,247
  • 1
  • 37
  • 52