Solved

How To Tweak Digit Length

Posted on 2010-09-01
5
420 Views
Last Modified: 2012-05-10
I have a bigINT column in SQL server where the digit length is 14. How and where can I tweak the digit length in SQL Server 2008. Would like this system created bigint column to default to 10 digits.
0
Comment
Question by:jazzcatone
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 32

Expert Comment

by:bhess1
ID: 33579358
If you want a specified number of digits, choose a DECIMAL column.   In SQL Server, a BigINT column is an 8-byte integer, which is good to 14 digits.

Alternately, you could apply conditions on the row to only allow values between 1000000000 and 9999999999, so other values would return errors.
0
 
LVL 41

Accepted Solution

by:
ralmada earned 250 total points
ID: 33579364
You can convert it to varchar and truncate the digits
select left(cast(yourcol as varchar), 10)
or you can divide the bigint number by 10000
select yourcol / 10000
If that's not what you want, please clarify with an example
0
 
LVL 3

Expert Comment

by:Blood
ID: 33579435
When you say "system created" do you mean you told it to create a new column called "X" and just took the default of bigINT or are you talking about some system column?
0
 
LVL 58

Assisted Solution

by:cyberkiwi
cyberkiwi earned 250 total points
ID: 33584223
If you mean you have a bigint IDENTITY column, then a number cannot be definition have leading zeroes.
However, you can force it to start at 1 billion which is the smallest number with 10 digits.
You can reseed the identity using something like this

create table xy (i bigint identity(1,1), other varchar(10))
dbcc checkident(xy, reseed, 1000000000)
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…

729 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question