Random populate a field

[gfrlaser]

I have a new project where the client needs to build random alpha numeric characters of 10 digits to comply with a sweepstakes law.

The specs are we use the any letters except "I" (looks like a one) and no 0's or 1's. The thought is to have a maximum of only 4 alpha characters per string with the rest contain numerals.

I will have to build 1,000,000 records. Any way to build something like this fairly quickly using the random function?

Thanks.
Will

 

[Mike Lewis]

Hi Will,

Not sure exactly what combination of letters and digits you want. The following will give you a million records, each containing a string of ten mixed letters and digits excluding 0, O, 1 and I.

This is off the top of my head, and not tested:

lcGoodChars = "ABCDEFGHJKLMNPQRSTUVWXYZ23456789"
CREATE TABLE Numbers (RandomNum C(4))
FOR lnJ = 1 TO 1000000
  lcRandom = ""
  FOR lnI = 1 TO 4
    lcRandom = ;
	  lcRandom + ;
	  SUBSTR(lcGoodChars, INT(RAND() * LEN(lcGoodChars)), 1)
  ENDFOR 
  INSERT INTO Numbers (RandomNum) VALUES (lcRandom)
ENDFOR

Note that this is not truly random. The randomness is probably good enough for most purposes, but perhaps not for something as strict as a lottery law.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[gfrlaser]

This looks like a great start!

I modified it a little because I need 10 total characters in the string.

lcGoodChars = "ABCDEFGHJKLMNPQRSTUVWXYZ23456789"
CREATE TABLE Numbers (RandomNum C(10))
FOR lnJ = 1 TO 1000000
lcRandom = ""
FOR lnI = 1 TO 10
lcRandom = ;
lcRandom + ;
SUBSTR(lcGoodChars, INT(RAND() * LEN(lcGoodChars)), 1)
ENDFOR
INSERT INTO Numbers (RandomNum) VALUES (lcRandom)
ENDFOR

One thing I dont understand is why some have 9 characters as opposed to 10. Is there a way to limit this to only 3 alpha characters, the rest being numerical?

Thanks for your help!
Will

 

[Mike Lewis]

Will,

My mistake. I put wrote 4 instead of 10. Don't know why.

To get 3 letters and 7 digits, you could have two character lists:

lcLetters = "ABCDEFGHJKLMNPQRSTUVWXYZ"
lcDigits = "23456789"

Then, in the inner loop, test to see if lnI is less than or equal to 4. If it is, use lcLetters, else lcDigits.

As for why some are only 9 chars, it might be because INT(RAND() * LEN(lcGoodChars) is coming out as zero occasionally. Perhaps you need to add one to it.

Actually, I'n not completely sure this approach is correct. It will give a list of a million random numbers, but then what? If your aim is to generate a winning number for a lottery ticket, wouldn't it be better just to generate a single random number? Or have you actually got a million winners?

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[gfrlaser]

No, one winner, but 1000000 are automatically entered and will have to go to a web site and enter the number to see if they won. What you have given me will work. Thanks.

To top it off, now it seems I need to figure out how to add a check digit with mod 10. That one is a bit above me as well.

 

[Mike Lewis]

now it seems I need to figure out how to add a check digit with mod 10

I see you've now had an answer to that in another thread, so I won't bother to answer it here.

Please keep in mind that the code I showed you here will not generate true random numbers, in the strict sense of the term. No program can do that. I'd guess that the numbers would be sufficiently random for your purposes, but if true randomness was critical, you'd have to find some other way of doing it. I believe most national and state lotteries rely on some sort of mechanical generator, involving bouncing balls, rotating drums, etc., or electronic devices like ERNIE (see

http://en.wikipedia.org/wiki/ERNIE#ERNIE).

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[Olaf Doschke]

Mike, you've got a point, but if you generate all the codes printed on lottery tickets it does not really matter if they are really random or not. You could even print the sequential numbers from 000000 to 999999. I think the point is to verify entered numbers, if they were really printed on a ticket. And therefore I think the MOD 10 checksum is not suficient, as even without the knowledge of how to compute the check digit any 1 out of 10 randomly generated numbers will comply to this. You should consider a stronger check mechanism.

On the other side a really simple check would be to look in the table of all genereated lottery codes, if the code was really generated and then the check digit may be sufficient as a first simple and fast check.

As Mike said you already know how to do that mod 10 check digit there's only one thing to add: Now the algorithm generating the codes is public you should at least do one =RAND(-1) as the first command of your generating code. Because then nobody can regenerate the same set of million codes, and even though they are still not really random they will depend on a seed depending on your system time amongst others and so will not be reproducable easily.

Bye, Olaf.

 

[gfrlaser]

I've read somewhere else about that point, starting with rand(-1). Fortunately this is not for a lottery. Its for a retail chain that is going to hold a sweepstakes. the law says the numbers must be random, that is not a problem now with the above code supplied by Mike. I will add the -1 to the code like you suggest Olaf. I am not familiar with how check digits work however and thought a set routine was required in order for check digits to be truely valid in the mathmatical sense. I also thought check digits contain two numbers, not one. Thanks for all the ideas.

 

[Mike Lewis]

Will and Olaf,

I agree that adding a seed value of -1 will improve the apparent randomness. However, be sure to do that only once, in the first iteration. If you were to call RAND(-1) a million times in close succession, you'd get long runs of the same number being generated.

Will, you say that "the law says the numbers must be random". Does it define "random" in any way? Or, more specifically, does it define a test that would determine if the numbers were sufficiently random? It would be interesting to see if this method meets any such test. I'm only asking out of curiosity.

Mike




__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[gfrlaser]

I have not seen the letter of the law. I am under the assumption that if I were to generate 1,000,000 seperate strings or characters that they would need to be unique and not sequential, hence random. To test it I simply indexed unique. Duplicates were generated about a rate of 48 or so each time. If I generate 1,001,000, I am able to eliminate the duplicate records to where I have what I need. I suppose if they ask, I would just have to turn the numbers over to them and they would test them themselves. I don't know, new territory to me.

 

[Mike Lewis]

new territory to me.

And for me too. I was only asking about the law out of curiosity. Let us know if you find out anything more.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[cricket]

On a pedantic point, do you destroy randomness by deleting (and presumably replacing) duplicates?

 

[Olaf Doschke]

Well, if it's a requirement, that each code is unique, there is no point in arguing that duplicate values should be allowed for real randomness...

Bye, Olaf.