How can I generate unique random CHAR(6) with values “AAAAAA” to “ZZZZZZ”?

Question

I'm not a sophisticated developer, as some of you already know, but maybe you could help me out with a rough design for this?

I would like to mimic the way airline reservation systems randomly generate unique record locators for their primary keys. None of the generated values can form obscene words, other specified values, or be a duplicate in an existing row, so a separate lookup table containing the prohibited values is required?

EDIT: Would it be better to populate a table with valid keys and randomly select a row which has not been updated with a key_already_used flag in order to generate the value, or should the value be randomly generated by a user-defined function, looked up to see if its not in the prohibited values table, then looked up to see if it has not already been inserted in the reservations table?

Letters offer more possible combinations (6!26 - prohibited values) versus (6!10) and letters are easier to remember than numbers? — Joe R., Jul 12 '12 at 01:05
The implementation of this sounds similar to what many URL shorteners do. Check this question out for some possible ideas that may help you http://stackoverflow.com/questions/742013/how-to-code-a-url-shortener — Marshall, Jul 12 '12 at 03:17
@Marshall: url-shortener implies creating a function which converts Base 26 numbers to letters. Would this be a good approach for my goal? — Joe R., Jul 12 '12 at 03:39
"Would it be better to populate a table with valid keys" - 26^6 is almost 309 million keys so not a good solution. Just generate the key and then check to see if it's already used. — DJ., Jul 12 '12 at 03:40
Why "random" and not just incremental from AAAAAA to ZZZZZZ? after all you need uniqueness ¿don't you? — Luxspes, Jul 12 '12 at 03:43
It needs to be random, incremental is too predictable. Customers could figure it out and try to use someone elses key. Maybe I should even add an aditional CHAR as a modulo (check-char)? — Joe R., Jul 12 '12 at 03:48
This is sort of a similar program I wrote in ole QBASIC to generate unique incremental numbers http://stackoverflow.com/questions/9935974/in-sql-how-can-i-generate-every-possible-unique-combination-of-556 — Joe R., Jul 12 '12 at 03:59
It's peculiar that in Informix 11.50 documentation I didn't see a random function, yet I spotted a ROUND function which can round DATE's and DATETIME's! — Joe R., Jul 12 '12 at 04:33

Luxspes · Answer 1 · 2012-07-12T04:12:36.013

1

Here is a good example.

To convert from int to char sequence:

create function CustomerNumber (@id int) 
returns char(5) 
as 
begin 
  return char(@id / power(26,3) % 26 + 65) + 
     char(@id / power(26,2) % 26 + 65) + 
     char(@id / 26 % 26 + 65) + 
     char(@id % 26 + 65) 
end

And then all that you have to do is pass an unique integer and you will get an unique string (or a random integer, and you will get a random string)

(just remember that random is not the same as unique)

I guess in informix it should look like this?

CREATE FUNCTION CustomerNumber (id int)
RETURNING char(5);
 RETURN integer::char(id / POW(26,3) % 26 + 65) + 
         integer::char(id / POW(26,2) % 26 + 65) + 
         integer::char(id / 26 % 26 + 65) + 
         integer::char(id % 26 + 65) 
END FUNCTION

edited Jul 12 '12 at 04:12

answered Jul 12 '12 at 03:49

Luxspes

6,268
2
28
31

Is this example how you would do it in MySQL? – Joe R. Jul 12 '12 at 03:52
1

No.. this is SQLServer... but you question has no MySQL tag... oooh I see you have an informix tag... sorry, no informix skills here... but I'll see if I can find something... – Luxspes Jul 12 '12 at 03:53
1

hopefully it is pretty much correct... – Luxspes Jul 12 '12 at 04:13
(Off this topic) I see that you feel SQL is full of flaws, perhaps later on you'd care to add your two cents in my question: http://stackoverflow.com/questions/3076855/what-deficiencies-do-you-feel-exist-with-sql-and-what-changes-would-you-make-to – Joe R. Jul 12 '12 at 04:13
(On topic) In your sample func, what does the POW mean?.. Oh, didn't see your edited equivalent informix func.. power(), hmm i'll try it. – Joe R. Jul 12 '12 at 04:15
1

The POW function raises its first numeric argument, the base, to the power of its second numeric argument, the exponent: http://publib.boulder.ibm.com/infocenter/idshelp/v115/index.jsp?topic=%2Fcom.ibm.sqls.doc%2Fids_sqs_1477.htm – Luxspes Jul 12 '12 at 04:17
1

(off this topic) answering your SQL flaws question – Luxspes Jul 12 '12 at 04:17

How can I generate unique random CHAR(6) with values “AAAAAA” to “ZZZZZZ”?

1 Answers1