On Aug 16, 2009, at 5:07 AM, Thom Brown wrote:
Does anyone know a way to generate a random and unique lowercase alphanumeric ID (preferably without using 0, 1, o or i to prevent problems with users manually typing the ID) using SQL without resorting to a prerendered table or using GUIDs.
For example, if I were to ask for an ID of 5 characters, something like the following would be returned:
hn21o
8sp2j
9wwun
m7z02
Notice that I don't mean hexadecimal values either. This would preferrably not resort to trying to generate the ID, then checking for a clash, and if there is one, do it again, although that could do as I can't think of how the ideal solution of a ID hashing algorithm would be possible.
One way is to use a LFSR (linear feedback shift register function). I haven't used one in a long time but I recall generating pseudo random numbers that are guaranteed not to repeat after billions of iterations. It's very fast as well. Then translate the resulting integer into the character sequence of your choosing. Here is a reference:
http://en.wikipedia.org/wiki/Linear_feedback_shift_register
Bob Gobeille
Hewlett Packard
Open Source Program Office