Alphanumeric Primary Key Generator

Question

Alphanumeric Primary Key Generator

Howard Rybko

Old Hand

Points: 342
More actions
May 15, 2006 at 12:04 am

#93911

Hi
I wonder if someone smarter than I am can please help me.
I have been battling to create a AphaNumeric key that goes like this:
AAA001 ... AAA002 ... AAA003 (then AAA999 .... AAB001 ...)
I did find a solution that nearly did it using an insert trigger combined with identitiy key:
create trigger i_customidentity on tbl_custom_identity for INSERT as
update tbl_custom_identity
set Custom_Key = CHAR (ASCII ('A') + ((Increment - 1)/ (26 * 26)) % (26 * 26 * 26)) +
                                CHAR (ASCII ('A') + ((Increment - 1) / 26) % (26 * 26)) +
                                CHAR (ASCII ('A') + (Increment - 1) % 26) +
                                RIGHT('00' + cast(110 + Increment as varchar), 3)
where Increment = @@IDENTITY
This was from an article here http://www.igluon.net/iGluon_Articles/custom_primary_key.asp
Problem is that the algorithm returns:
AAA001 ... AAB002 ... AAC003 ..
and no matter what i try i cannot seem to get it to behave the way i need.
Any help will be gratefully appreciated.
Thank you
Howard

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Hans Lindgren SSChampion Points: 10496 More actions · Answer 1

The algorithm you found seems even worse after the second glance. If you substitute 999 as 'Increment' you get: BgK109 which does not seem at all correct.

Hans Lindgren SSChampion Points: 10496 More actions · Answer 2

Edit: This solution does not work either...

Okay. Try the below trigger instead. It should work in all cases (except for all the cases when the algorith overflows). You can either calculate where you will get errors or peruse a join with your Numbers table for algorithm errors...

Happy hunting!

Hanslindgren

New Trigger:

CREATE TRIGGER i_customidentity ON tbl_custom_identity FOR INSERT AS

UPDATE tbl_custom_identity

SET Custom_Key = CHAR (ASCII ('A') + (((Increment - Increment % 1000) / 1000 - 1)/ (26 * 26)) % (26 * 26 * 26)) +

CHAR (ASCII ('A') + (((Increment - Increment % 1000) / 1000 - 1) / 26) % (26 * 26)) +

CHAR (ASCII ('A') + ((Increment - Increment % 1000) / 1000 - 1) % 26) +

RIGHT('000' + CAST(Increment % 1000 AS VARCHAR(10)), 3)

WHERE Increment = @@IDENTITY

Hans Lindgren SSChampion Points: 10496 More actions · Answer 3

Okay. Finally. Overflow = 17575999 + 1

And this is the working trigger:

CREATE TRIGGER i_customidentity

ON tbl_custom_identity

FOR INSERT

AS

UPDATE tbl_custom_identity

SET Custom_Key =

CHAR (ASCII ('A') + (Increment - Increment % 1000) / 676000) +

CHAR (ASCII ('A') + ((Increment - Increment % 1000) % 676000) / 26000) +

CHAR (ASCII ('A') + ((Increment - Increment % 1000) % 676000) % 26000 / 1000) +

RIGHT('000' + CAST(Increment % 1000 AS VARCHAR(10)), 3)

WHERE Increment = @@IDENTITY

You could test it by rewriting it into a select clause and using these test values:

SET @Increment = /*999*/999

SET @Increment = /*AAZ*/25 * 1000

SET @Increment = /*AAZ*/25 * 1000 +/*999*/999

SET @Increment = /*AZA*/25 * 26000

SET @Increment = /*AZA*/25 * 26000 + /*AAZ*/25 * 1000

SET @Increment = /*AZA*/25 * 26000 + /*AAZ*/25 * 1000 +/*999*/999

SET @Increment = /*ZAA*/25 * 676000

SET @Increment = /*ZAA*/25 * 676000 + /*AZA*/25 * 26000

SET @Increment = /*ZAA*/25 * 676000 + /*AZA*/25 * 26000 + /*AAZ*/25 * 1000

SET @Increment = /*ZAA*/25 * 676000 + /*AZA*/25 * 26000 + /*AAZ*/25 * 1000 + /*999*/999

Good luck!

Hanslindgren

Stewart Joslyn SSCertifiable Points: 6131 More actions · Answer 4

Why bother - its only a key and any manipulation will slow things down. Why not keep it numeric then, if you need to display it, convert it to an alphanumeric representation at that stage.

Hans Lindgren SSChampion Points: 10496 More actions · Answer 5

Speed is not a one way measure. Remember that not all things that are slower then others are inherently bad.

Stewart Joslyn SSCertifiable Points: 6131 More actions · Answer 6

Agreed. ... and there may be a good reason for doing this - I was curious as to what it was. If there isn't a need for it, though, then surely its better not to do it in the first place. That way also is simpler which makes maintenance easier - and that often can be the key consideration when deciding between reasonably close alternatives.

David Burrows SSC Guru Points: 65142 More actions · Answer 7

Deja Vu

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=235707

Far away is close at hand in the images of elsewhere.
Anon.

Hans Lindgren SSChampion Points: 10496 More actions · Answer 8

Haha. I wish I had did a search before I started on the trek of finding a solution

Thanx

Howard Rybko Old Hand Points: 342 More actions · Answer 9

Hi Hans

THANKS A MILLION!! for your efforts. I really appreciate the time you have spent.

I did actually find the previous post but I had no luck getting it to work either...

Thank you

Howard