July 1, 2005 at 9:42 am
Instead of using the datatype "int IDENTITY (1, 1)" as the primary key in a table, I have a need to use the following text-based auto-numbering-like 5-characters based format:
00001
00002
00003
...
99998
99999
I have to store it as "char(5)" with those leading zero's. What's the easiest way to auto-generate these types of values? Is there a way to somehow use a datatype like "char(5) IDENTITY (00001, 1)" or something like this?
It's easy to INSERT a record using "int IDENTITY (1, 1)" because the system automatically issues the next sequential number. But, if I use "char(5)", it's not so easy to keep track of what's the next number to issue for an INSERT statement. The INSERT statement will now need to somehow figure out the next unique "char(5)" value to use for the record to be INSERTed.
Thanks.
July 1, 2005 at 11:54 am
Ok. I'll bite. IF you know the value is going to be a 5 digit number and you want to DISPLAY (assumption here) it as 0000n why not set the IDENTITY and then DISPLAY as CHAR(5)??
Good Hunting!
AJ Ahrens
webmaster@kritter.net
July 2, 2005 at 11:54 am
If you absolutely HAVE TO HAVE an autonumbering text based field, one way you could do it is by having your IDENTITY column and then adding a computed column (textID) to your table where the formula would be:
RIGHT('0000' + cast(ID as varchar), 5)
But what will you do when you reach 100000 ?!?!
**ASCII stupid question, get a stupid ANSI !!!**
July 2, 2005 at 10:04 pm
Here's another stretch:
KeyVal int not null IDENTITY(100000,1).
This would give you integer values of
100001
100002
100003
100004
100005
and so forth. Convert to strong, lop off the first character with
substring(cast(KeyVal as char(6)), 2, 5)
and you've got your value.
Philip
July 3, 2005 at 9:41 am
Philip's solution is much better - this way there won't be a need to have 2 columns!
**ASCII stupid question, get a stupid ANSI !!!**
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply