Showing field in 7 digit format

Posted on 2004-05-05
Last Modified: 2008-02-01
Hi all,
I have a field with the following datatype: decimal-->precision (7) , scale (0), identity(yes), identityseed(1), increment(1) and here is what i want:
i want the field store 0000001 for the first record, 0000002 for the second one and so on.
I dont want it to ignore the first 6 digits. Is this possible? Or if there is another way to do it please advice.
Thanks in advance,
Question by:e106199
  • 2
  • 2
  • 2
LVL 69

Expert Comment

by:Scott Pletcher
ID: 10998597
Leave the "base" field as numeric, but create a computed column to give you the display format you want.  For example:

-- say identity field is called "id"

ADD idDisplay AS RIGHT('000000' + CAST(id AS VARCHAR(7)), 7)

Then use column name idDisplay rather than id:

SELECT idDisplay, ...
FROM yourTable

This also makes it easier to change later if you need, say, 8 digits instead of 7 (you've gone international and added a *lot* of rows :-) ).

Expert Comment

ID: 10998710
If you dont need this on the Table side you can always do this in your front end when you are displaying the data.
You have to write a little code that if your number is One digit then add 5 Zeros and if it is 2 digits then add 4 Zeros....etc
and if your number is 6 digits (222222) add no zeros, since we dont want 000000222222, or maybe we want that....

ANy how the VB program is easy if you want the display in front..... if you want it on the table then you have to go with ScottPletcher  comment

Author Comment

ID: 10999007
Hi apirnia,
I need it on the Table side, ppl should be able to make searches,editings depending on it and i dont want to write code every time I need to access this field. Thanks anyway.

And Scott,
isnt your Alter Table adding 6 zeros whatever the number is? If the number reaches to a 2 digit number the new number should be 00000**. so the total digits will always be 7. If the number reaches to a 7 digit number than there shouldnt be any zeros before the number.

And the second thing is: The ppl are going to insert records from an page. Each time a record is inserted the id field will increment by 1. How can i insert the new iddisplay record at that point.Where should i put the Alter Table command so it will automatically be created at the time id is created. Like the new record will trigger the slq server to run the Alter command.
Any help will be appreciated.
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

LVL 69

Accepted Solution

Scott Pletcher earned 200 total points
ID: 10999054
>> isnt your Alter Table adding 6 zeros whatever the number is? <<
Yes and no :-).

RIGHT('000000' + CAST(id AS VARCHAR(7)), 7)

Say id is 123.  The string created will be:


But also notice that there is a RIGHT function that uses only the 7 right-most positions, so the *final* result is:

0000123  -- a total of 7 bytes counting from the RIGHT of the string

Similarly, for 10001, the intermediate result would be:

00000010001  the final result would then be


Note that the ALTER TABLE ... ADD ... AS statement only has to be run *once*, then the column is permanently defined just like any other column.

Author Comment

ID: 11001032
Hi Scott,
I dont know much about ms sql server or any db.
Where do i run the ALTER TABLE ... ADD ... AS statement ?Will it be somewhere on the sql server or somewhere in my code? After a record is inserted, the id will be created automatically and iddisplay will be derived from the id field. So i should do the insert first. If the iddisplay is created on the server side, its great. But if i have to run the Alter statement(in my code) after the record is inserted i have a couple concerns about it.
Thanks for the great help.

Expert Comment

ID: 11001047
There is something called QueryAnalyzer.

Go to Strat >> Microsoft Sql Server >> QueryAnalyzer.

After you login to your database you pick a your table from the drop down and just use that environment.

I think you might be able to do it in Enterprise manager as well.

Open your Table >> Return all rows >> click on the SQL button on the bar and write it there.

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
unable to reinstall SCVMM 2012 R2 - SQL errors. 5 34
SQL Rewrite without the NULLIF 4 25
Need time in SQL 8 30
SQL Server 2008 Std. License Key owner or vendor 4 53
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how the fundamental information of how to create a table.

685 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