A function that can randomly pick an integer from a given range can be used by applications that need a number dice roll and random generation of strings, numbers, and even random generation of complex passwords. Let's say you would like to randomly pick a number from 1 to 10. This gives us 10 choices to randomly pick, not 9 as can be mistakenly thought of since 10 minus 1 equals 9. The same is with the range of choices from 0 to 10. This gives 11 possibilities, not 10. To illustrate:

- The range of choices should include the value of the lower end of the range
- It should also include the value of the higher end of the range

To generate a random number from a given lower value and a higher value, use the formula below, where minRange is the lower end of the range and maxRange is the higher end of the range:

minRange + FLOOR(RAND() * (maxRange - minRange + 1))

The formula gives

*minRange*a random integer to add with, where the return from the RAND() function is passed to the math flooring function. The return from RAND() is multiplied with the difference between

*minRange*and

*maxRange*to set the decimal range that can be randomly created. The + 1 added to the difference between the two values is needed so that the range includes the

*maxRange*number itself. This addition of an extra 1 can easily be missed which results in erroneous results especially when the lower value for the range is a negative number.

To illustrate the common mistakes on how a formula for a simple random range picker often leads to a wrong analysis, let us first fill the values with

*minRange = 1*and

*maxRange = 10*. This gives us:

SELECT 1 + FLOOR(RAND() * 10);

The common mistake here is that some devs will think a range from 1 to 10 should result in a formula that would include "RAND() * 9" when they use a range formula of

*10 - 1 = 9*.

And filling the values with

*minRange = -10*and

*maxRange = 10*gives us:

SELECT -10 + FLOOR(RAND() * 21);

The common mistake happens when a quick mental analysis of a range formula is made, some devs think that the solution for the second formula will result to "RAND() * 20".

The given formula is used in the function below. The integers for

*minRange*and

*maxRange*can be positive or negative, including zero. The range of numbers returned will correctly include the values of

*minRange*and

*maxRange*as was explained above.

DELIMITER $$ DROP FUNCTION IF EXISTS `randomRangePicker` $$ CREATE DEFINER=`root`@`localhost` FUNCTION `randomRangePicker`( minRange INT, maxRange INT) RETURNS int(11) BEGIN DECLARE pick INT; SET pick = minRange + FLOOR(RAND() * (maxRange - minRange + 1)); RETURN pick; END $$ DELIMITER ;

To use the function, simply pass the two needed parameters like:

SELECT randomRangePicker(-1, 1);

This will return either -1, 0, or 1.

See the following user defined functions for samples where randomRangePicker() can be used:

Updated: 10/14/2012

## No comments:

Post a Comment