#MYSQL GENERATE RANDOM EMAIL MOD#
#MYSQL GENERATE RANDOM EMAIL FULL#

Obviously you could expand on that and generate random months or even full dates if you really wanted to. SELECT FirstName,MiddleName,LastName, DATENAME(WEEKDAY,ABS(CHECKSUM(NEWID()))%7 + 1) AS RandomDay This is something that I’ve used many times for various reasons, a random day of the week can easily be generated using the following code. SELECT FirstName,MiddleName,LastName, ABS(CHECKSUM(NEWID()))%10 + 11 AS RandomNumberĪ Few Funky Things You Can Do With Random Numbers

For example, for a range of 10-20, you’ve got 10 values in that range and with the lowest value being 10, you could use the following…

#MYSQL GENERATE RANDOM EMAIL MOD#

That too is very simple, all you need to do is mod by the number of values in your range and add to that that lowest number of the range plus one. You might want to generate some random numbers in the range of 10-20.

SELECT FirstName,MiddleName,LastName, ABS(CHECKSUM(NEWID()))%10 + 1 AS RandomNumber So if we want to return a random number between 1 and 10 all we have to do is mod our number by 10 and add one (we need to add one because 10 mod 10 would really give us numbers between 0 and 9 and that’s not what we really want at all). Basically, using modulus, we can get the remainder of a number after it’s been divided by a specified value. or, if you were anything like me, you were the one dodging those pens) and remember a little thing called modulus. Wanna Take It Further, What About Generating Numbers In A Range?īut what if we don’t want those crazily big numbers, what if we only want to generate numbers up to say 10? Well for that you’re going to have to think back to your school maths class (when you weren’t throwing pens at the class nerd or chatting up the fit blonde…. SELECT FirstName,MiddleName,LastName, ABS(CHECKSUM(NEWID())) AS RandomNumberĪnd there we have it, a different random number generated for each row of our result set. ABS() returns the absolute value of a number or for you and me, it gets shot of that minus sign. But once again SQL Server can come to our rescue, this time with the ABS() function. That’s looking better, now we’ve got a number! You know what?! I’m still not happy (I know I’m a difficult guy to please), I don’t want to be seeing those nasty negative numbers. SELECT FirstName,MiddleName,LastName, CHECKSUM(NEWID()) AS RandomNumber Let’s have a look and see what that’s looking like now…. The nice thing for us is that the checksum returned is a numeric value. Now CHECKSUM() is a little known function that’s designed to return a checksum or hash value for a given expression. But there is something that we can do to make things better, CHECKSUM(). Now, that’s looking better, we’ve now got uniquely random values for each of the people… but I still don’t like that, it’s a horrible GUID, what am I supposed to do with that?! Wouldn’t it be nice if we could get an ordinary looking number out? Perhaps CAST or CONVERT it to an INT? I’ll tell you now, that’s not going to work. SELECT FirstName,MiddleName,LastName, NEWID() AS RandomNumber Let’s try the same query but this time we’ll swap RAND() with NEWID(). So what else can we do? We’ll there is something that gives us a ‘random’ value for every row in the query and that’s our good old friend NEWID(). And that’s my issue with RAND(), it’ll give you a different random number every time it runs but if run as part of a query it’ll always return the same number for every row returned. It looks like we’ve got the same number for every person, that wasn’t what we wanted. SELECT FirstName,MiddleName,LastName, RAND() AS RandomNumber My major gripe with RAND() is what happens if we want to generate a random number for every row of a query, lets see what happens if we run the following against our trusty old workhorse, AdventureWorks and try to assign a random number to everyone in the person table. Well that’s an easy one isn’t it? We can use RAND(). How do we get SQL Server to give us random numbers for each row returned by a query?

Here’s a lunchtime quickie for you all, this is something that I’ve seen asked on the forums plenty of times and always gets some quite convoluted responses. UPDATE: Check out Generate a Random Number for Each Row in a Query for an alternative method that I now use for generating random numbers using CRYPT_GEN_RANDOM

Mysql generate random email