CODEWITHSUNDEEP
sqlsql-serversql-server-2008sql-server-2008-r2
Midhun Mathew
Midhun Mathew

Reputation: 319

How to restrict the length of INTEGER when creating a table in SQL Server?

When creating a table in SQL SERVER, I want to restrict that the length of an INTEGER column can only be equal 10.

eg: the PhoneNumber is an INTEGER, and it must be a 10 digit number.

How can I do this when I creating a table?

Upvotes: 7

Views: 88993

Answers (8)

Rahul Tripathi
Rahul Tripathi

Reputation: 172578

I would recommend you to use varchar to store phone numbers(only for phone numbers as phone numbers may contain hyphens, plus signs) and restrict the length to 10 ie, varchar(10).

As correctly pointed by a_horse_with_no_name in comments you can put constraint on the numbers to be of specified range like this:

check (phone_number between 0 and 9999999999)

A side note:

You will receive an error message like this if you use numbers outside the range of int -2147483648 through 2147483647

Arithmetic overflow error converting expression to data type int.

So you will not be able to use all possible integers of length 10 in your case.

Upvotes: 1

ibozkurt79
ibozkurt79

Reputation: 253

My recommendation is:

CREATE TABLE trial_table (phone_number VARCHAR(13));

The column can be used for international numbers too.

Upvotes: 0

sangram
sangram

Reputation: 415

As per me Phone number should not be stored in integer as we are not going to do any numeric operation on it like adding dividing .we are going to treat it as string for e.g. finding all number with ISD say '91' or STD '022' etc secondly if you switch to make it integer you have to handle overflow

Upvotes: 1

anonxen
anonxen

Reputation: 824

If I understand correctly, you want to make sure the entries are exactly 10 digits in length.

If you insist on an Integer Data Type, I would recommend Bigint because of the range limitation of Int(-2^31 (-2,147,483,648) to 2^31-1 (2,147,483,647))

CREATE TABLE dbo.Table_Name(
    Phone_Number BIGINT CONSTRAINT TenDigits CHECK (Phone_Number BETWEEN 1000000000 and 9999999999)
    );

Another option would be to have a Varchar Field of length 10, then you should check only numbers are being entered and the length is not less than 10.

Upvotes: 3

Thorsten Kettner
Thorsten Kettner

Reputation: 95072

First consider internal and external format:

Yes, a telephone number can be stored as an integer. You would have to assure however that all numbers are stored in the same format, e.g. as the international number without the plus sign. 4940123456 would then be a German number for instance, as 49 is the German country code. To analize the number later, however, would be difficult; country codes can be 1 to 4 digits, followed by a presumably unknown number of area code digits. But just to know a number and not to know its structure may be sufficient for your purposes. With check constraints you could assure that the number is positive and not longer than, well, how long is the longest number allowed? Be aware: Everytime you show the number, you may have to format the output (in the example given: add a leading plus sign to the number).

The other way would be to store phone numbers as strings. That would make it possible to store numbers such as '+49-40-123456'. Then the internal format is the same as the external. Advantage: You wouldn't have to think of formatting the output everytime you show the number. But you could even change the format on output if you wanted (remove dashes or replace the plus sign with the actual county dial code or remove country and area code for local calls, etc.) You would have to decide whether to enforce a certain format or not. If not, then numbers could look very different '123456', '004940123456', '040/123456', ... To enforce a certain format, you would write a function (because of the complexity of such a format) and use that in a check constraint. Or write an insert trigger (this should be a BEFORE INSERT trigger, because you want to change a value; as T-SQL doesn't provide this, you would use an INSTEAD OF INSERT trigger instead) to have the field formatted as you desire.

Upvotes: 0

Navneet
Navneet

Reputation: 447

Make column varchar and create a check that it strictly should have 10 characters 

create table some_table
(
   phone_number varchar(10) check (len(phone_number)=10)
);

Upvotes: 0

user330315
user330315

Reputation:

If you want to limit the range of an integer column you can use a check constraint:

create table some_table
(
   phone_number integer not null check (phone_number between 0 and 9999999999)
);

But as R.T. and huMpty duMpty have pointed out: a phone number is usually better stored in a varchar column.

Upvotes: 16

huMpty duMpty
huMpty duMpty

Reputation: 14470

I don't think there is a way to limit if you use number fields like int, bigint, smallint, and tinyint

Make a varchar(10) field and validate before insert

Still you need to use int field to store the phone number, you will need to restrict before in your application

Upvotes: 0

Related Questions