which datatype to use to store a mobile number

Question

Which datatype shall I use to store mobile numbers of 10 digits (Ex.:9932234242). Shall I go for varchar(10) or for the big one- the "bigint".

If the number is of type- '0021-23141231' , then which datatype to use?

Turf

Answer 1

• varchar/char long enough for all expected (eg UK numbers are 11 long)
• check constraint to allow only digits (expression = NOT LIKE '%[^0-9]%')
• format in the client per locale (UK = 07123 456 789 , Switzerland = 071 234 56 78)

Bagwig

Answer 2

As others have answered, use varchar for data that happens to be composed of numeric digits, but for which mathematical operations make no sense.

In addition, in your example number, did you consider what would happen if you stored 002123141231 into a bigint column? Upon retrieval, it would be 2123141231, i.e. there's no way for a numeric column to store leading 0 digits...

Slurp

very good answer. indeed no calculation will be ever done on a phone number :P – Facesaving

Answer 3

Use varchar with check constraint to make sure that only digits are allowed.

Something like this:

create table MyTable
(
    PhoneNumber varchar(10)
        constraint CK_MyTable_PhoneNumber check (PhoneNumber like '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]')
)

if it is always the same length you might want to use char instead.

Tenpins

Your check constraint only allows 10 numbers, so you might as well use a char anyway. – Fireplug

@ck - I agree. Although, this constraint is just and example. – Tenpins

Answer 4

varchar(50) is good for mobile number data type . because it may sometimes contain country code for example +91 or spaces also. For comparison purpose we can remove all special characters from both side in the expresion.

Suppliant