How can I create a unique but sequential number that is always six digits long?
Posted on 2005-04-15
I'm starting to populate my database and I need a way to create a unique identifying number/letter combination for each of my records. The format needs to be first a letter which is always the same and then a six digit number (ex. Z000001, Z000002 etc.) My database is set up with an ID field used as the Identity but for display purposes I need this type of name.
I'm thinking that I will only need to create this name at the point of the Insert statement so I need to somehow create this on the fly. It would be nice to have the @@Identity number be used in the name. My problem is how do I get it to always use six digits for the number?
Will I first have to do an insert statement and then query for the MAX(ID) as newID and then create a name with <cfset newName = "Z#newID#"> But how can I make sure that the newName will have the correct amount of numbers (six digits)? Any suggestions? Thanks.