PeterDownie.com

Back to the Library

Generate Random String

Generate a random string into an out variable! I use this for generating connctionsID.
status Has 3 possible values,MUST_BE_AT_LEAST_1_IN_LENGTH , MAX_LENGTH_255_CHARACTERS, SUCCESS

DELIMITER //
DROP PROCEDURE IF EXISTS generateRandomString//
CREATE PROCEDURE generateRandomString(IN INlength BIGINT UNSIGNED, OUT randomString VARCHAR(255), OUT status VARCHAR(255))
function: BEGIN
    DECLARE cur_randomItem TINYINT UNSIGNED;
    DECLARE cur_character CHAR(1);
    DECLARE var_small_letterStart TINYINT UNSIGNED DEFAULT (97 - 1); /* Starts at 1 not 0 */
    DECLARE var_capital_letterStart TINYINT UNSIGNED DEFAULT (65 - 1); /* Starts at 1 not 0 */
    DECLARE var_returnString VARCHAR(255) DEFAULT '';
    DECLARE cur_item TINYINT UNSIGNED DEFAULT 0;
    IF INlength = 0 THEN
        SELECT 'MUST_BE_AT_LEAST_1_IN_LENGTH' INTO status;
        LEAVE function;
    ELSEIF INlength > 255 THEN
        SELECT 'MAX_LENGTH_255_CHARACTERS' INTO  status;
        LEAVE function;
    END IF;
    L1: LOOP
        IF cur_item >= INlength THEN
            LEAVE L1;
        END IF;
        SELECT FLOOR(62 * RAND()) INTO cur_randomItem;/* 0 - 9, a-z,A-Z 10 + 26 + 26 + 10 */
        IF cur_randomItem < 10 THEN /* Create numeric values */
            SELECT cur_randomItem INTO cur_character;
        ELSEIF cur_randomItem > 10 AND cur_randomItem < 37 THEN /* Generate small letters */
            SET cur_randomItem = cur_randomItem - 10;
            SELECT CHAR(cur_randomItem + var_small_letterStart) INTO cur_character;
        ELSEIF cur_randomItem > 36 THEN
            SET cur_randomItem = cur_randomItem - 36;
            SELECT CHAR(cur_randomItem + var_capital_letterStart) INTO cur_character;
        END IF;
        SELECT CONCAT(var_returnString,cur_character) INTO var_returnString;
        SET cur_item = (cur_item + 1);
    END LOOP;
    SELECT var_returnString,'SUCCESS' INTO randomString, status;
END //
DELIMITER ;