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
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
  • 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.
Technology Partners: 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 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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

628 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