PHWinfo - Afficher un message - Selecting alphanumeric combinations that do not exist already intable?????

25/03/2008, 00h15

rbr (ryankbrown@gmail.com) writes:
> Hello all, I have an odd requirement. I have a column with a system
> generated username that is a 6 character, alphanumeric, field. These
> usernames are randomly generated by code. I need to create a stored
> procedure that will return all combinations that are not already
> being
> used. Maybe the result of still trying to wake-up from a long
> weekend.
> But, I cannot think of an easy way to do this.

All? There are 2176782336 combination, so unless a large number of the
combinations are already in use, that will be a huge result set.

I started to compose a solution that would return 1000 random codes
currently not, but I realised that it was not trivial to write. So I
abandoned it for the moment, as I may not solve the right problem for
you.

Of course, if you store all 2176782336 combinations in a table, it's
trivial. But would be over 20 GB in size... (Have to count with some
8-9 bytes overhear per row.)

Anyway, if you could be a little more specific, that would be nice.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

25/03/2008, 00h15	#4
Erland Sommarskog Messages: n/a Hébergeur:	Re: Selecting alphanumeric combinations that do not exist already in table????? rbr (ryankbrown@gmail.com) writes: > Hello all, I have an odd requirement. I have a column with a system > generated username that is a 6 character, alphanumeric, field. These > usernames are randomly generated by code. I need to create a stored > procedure that will return all combinations that are not already > being > used. Maybe the result of still trying to wake-up from a long > weekend. > But, I cannot think of an easy way to do this. All? There are 2176782336 combination, so unless a large number of the combinations are already in use, that will be a huge result set. I started to compose a solution that would return 1000 random codes currently not, but I realised that it was not trivial to write. So I abandoned it for the moment, as I may not solve the right problem for you. Of course, if you store all 2176782336 combinations in a table, it's trivial. But would be over 20 GB in size... (Have to count with some 8-9 bytes overhear per row.) Anyway, if you could be a little more specific, that would be nice. -- Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/pro...ads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinf...ons/books.mspx