Solved

How To Tweak Digit Length

Posted on 2010-09-01
5
422 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:Brendt Hess
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

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

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

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
In this article I will describe the Backup & Restore 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.
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

630 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