Solved

How To Tweak Digit Length

Posted on 2010-09-01
5
419 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

Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How can I use this function? 3 35
Replication failure 1 31
Need to trim my database size 9 49
when sa SQL Login becomes locked 6 24
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

738 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