In a recent post I used a certain method to generate some random text. However later on I found that the randomness of the text was limited.

When using the MD5 RAND function as below, it generates text that has a tendency to look similar, although it is still random. How to reproduce the problem? Run the following code and view the output several times. You will notice some characters appear more frequently. That is because the returned string is the result of a hash function.

select SUBSTRING(MD5(RAND()) FROM 1 FOR 25);

When using that code and examining the output over a series of trials it becomes apparent that some characters have a tendency to repeat themselves. Other characters seem to never appear. In cases where greater randomness is needed, this becomes a significant weakness.

Due to this, I have searched for an improved function. I created an alternative  by using a different approach. Instead of relying on the MD5 hash for randomness it uses rand() function to pick a random number within a range of numbers that represent the ascii values of upper case letters. Then it randomly decides whether to make it lower case character.

Keep in mind the below code does not guarantee that the results with include all upper or all lower cases, it is still possible for the text to become either of those although it is not likely.

PROCEDURE `getRandomLetters`(IN P_LENGTH INTEGER, OUT P_RAND_LETTERS VARCHAR(130))
BEGIN

DECLARE w_char VARCHAR(1) DEFAULT '';

DECLARE w_count,w_rand_num,w_i integer default 0;

DECLARE w_letters varchar(130) DEFAULT '';

--  should not exceed size of 130
if P_LENGTH<131 then
set w_count = P_LENGTH;
else
set w_count = 130;
end if;

WHILE  (w_i<w_count)  DO

		SET w_i=w_i+1;			

		-- 65 (A) - 90 (Z) in ascii 
		SET w_rand_num = ROUND((RAND() * (90-65))+65);

		-- make sure to select value that will 
		-- result in a number or letter

		SET w_char = char(w_rand_num using utf8);

		IF (ROUND((RAND() * (1)))=0) THEN

		SET w_char = LCASE(w_char);

		END IF;

		SET w_letters = CONCAT(w_letters,w_char);

end WHILE;

SET P_RAND_LETTERS = w_letters;

END