Many times, we need to create a random application id with some specific format and that should be unique. In this example, we are going to create an application ID and that should follow the below conditions.

  1. It should be Alphanumeric.
  2. Length should be 12. (First four character should be alphabets and the last eight digits must be numeric)
  3. Randomly generated.
  4. Should not contain 0, o, O, l, 1, I, i
  5. Last but not least, it should be unique.

So, first we are going to create a random string of length 4 and then will create random number of length 8 and then concatenate both the string and will check whether it is unique or not. If it is not unique then will generate new application ID.
All these things will take place inside custom MySQL function.

Step 1: Create a mysql function to generate string of length 4.

DELIMITER $$ 
CREATE FUNCTION randomStr() 
RETURNS varchar(128) 
BEGIN 
SET @chars = 'ABCDEFGHJKLMNPQRSTUVWXYZ'; 
SET @charLen = length(@chars); 
SET @randomString = ''; 
WHILE length(@randomString) < 4 
    DO 
        SET @randomString = concat(@randomString, substring(@chars,CEILING(RAND() * @charLen),1)); 
END WHILE; 
RETURN @randomString ; 
END$$ 
DELIMITER ;

Step 2: Create a mysql function to generate random number of length 8.

DELIMITER $$ 
CREATE FUNCTION randomNum() 
RETURNS varchar(128) 
BEGIN 
SET @digits = '23456789'; 
SET @digitLen = length(@digits); 
SET @randomNumber = ''; 
WHILE length(@randomNumber) < 8 
    DO 
        SET @randomNumber = concat(@randomNumber, substring(@digits,CEILING(RAND() * @digitLen),1)); 
END WHILE; 
RETURN @randomNumber ; 
END$$ 
DELIMITER ;

Step 3: Create a mysql function that will call the function created in step 1 and step 2 then will concatenate the output of both the function and check whether they exist in registration table or not, if they exist generate new application ID.

DELIMITER $$ 
CREATE FUNCTION GenUniqueAppID() 
RETURNS varchar(255) 
BEGIN 
SET @uniqueID = ''; 
SET @done = False; 
WHILE NOT @done 
    DO 
        SET @uniqueID = CONCAT(randomStr(),randomNum()); 
        SET @done = NOT exists(SELECT 1 FROM registration WHERE application_id = @uniqueID); 
END WHILE; 
RETURN @uniqueID ; 
END$$ 
DELIMITER ;

Now it’s time to test the function. Run the below sql and check the application ID.

select GenUniqueAppID();