Click here to Skip to main content
15,892,537 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
how to restrict no of digits in an integer column to 10 digits in sql server 2008 r2
Posted
Comments
Tomas Takac 26-Oct-15 5:45am    
Check constraint? So check your value >= 1000000000.

The maximum value for a 32-bits integer (int in SQL Server) is 2 147 483 647, so an integer number is already intrinsically maxed to 10 digits.

If you are working with a 32-bits integer field, you do not need to restrict it specifically.

If you are working with a 64-bits integer field, you can setup a validation rule on the column saying it must not go over 9 999 999 999.

In SQL language, that could be something like:
SQL
ALTER TABLE [YourTable]
ADD CONSTRAINT [Check_YourField] CHECK ([YourColumn] <= 9999999999)

Hope this helps.
 
Share this answer
 
You can't.
An integer in SQL is a specific data type, which holds a 4 byte value, and is thus between
-2,147,483,648 and 2,147,483,647
It cannot hold more than ten digits under any circumstances, and even then the topmost digit must be 0, 1, or 2.

If you want to store a full ten digits, then you would need a BIGINT which is an 8 byte value, but you can't restrict the length of that to ten digits either.

I would strongly suggest that you restrict your inputs at source, rather than trying to use you DB to do it - input validation is part of the user interface, after all.
And if you are trying to use this to store telephone numbers then don;t use a numeric format at all: use a NVARCHAR instead. Numeric columns are for values you can sensibly do maths with: and adding two telephone numbers together does not make any sense. Additionally, telephone numbers may contain non-numeric characters: "+44(0)1234567890" is a valid phone number.
 
Share this answer
 
This can be done by adding a CHECK Constraint for that column.

Something like-
SQL
ALTER TABLE YourTableName
ADD CHECK (YourColumnName <= 9999999999 AND YourColumnName >=-9999999999)

But it is still an incorrect use as max size of int in SQL Server is 2147483647.
So may be you have to verify your check condition.


Follow this link to know more about CHECK constraint-
SQL CHECK Constraint[^]
or
SQL SERVER – Create Check Constraint on Column[^]

Hope, it helps :)
 
Share this answer
 
v3

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900