Showing field in 7 digit format

Posted on 2004-05-05
Medium Priority
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 70

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 asp.net 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.
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 70

Accepted Solution

Scott Pletcher earned 800 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


Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

621 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