Issue with MySQL db Function and rand()

[Stoic Joker]

Greetings
    I've been fighting with this for a day or so and it giving me fits. I'm trying to get a MySQL db function to automatically generate a 7 character ID string during an insert command in the format of ABC1234. At this point triggering the function during insert is working fine, however the string generated is randomly - generated to short - between 4 and 7 characters in length.

So my question is how to I force this code to always give my a 7 character long string?

Code: Text [Select]

1. CREATE DEFINER=`Stoic Joker`@`%` FUNCTION `MakeUniID`() RETURNS char(7) CHARSET utf8
2. BEGIN
3. declare Result CHAR(7);
4. SET Result = '';
5.  
6. SET Result=CONCAT(
7.         substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ', rand(@seed:=round(rand(@seed)*4294967296))*26+1, 1),
8.         substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ', rand(@seed:=round(rand(@seed)*4294967296))*26+1, 1),
9.         substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ', rand(@seed:=round(rand(@seed)*4294967296))*26+1, 1),
10.         substring('0123456789', rand(@seed:=round(rand(@seed)*4294967296))*10+1, 1),
11.         substring('0123456789', rand(@seed:=round(rand(@seed)*4294967296))*10+1, 1),
12.         substring('0123456789', rand(@seed:=round(rand(@seed)*4294967296))*10+1, 1),
13.         substring('ABCDEFGHIJ', rand(@seed)*10+1, 1)
14.         );
15.  
16. RETURN Result;
17. END

It seemed to be a guaranteed no brainer when I swiped it from somewhere - and then modified it for my own purposes... But the damn thing just won't cooperate.

Thanks in Advance,

Stoic Joker

[Stoic Joker]

Okay, fine ... I'll answer my own question. Here's what finally worked:

Code: Text [Select]

1. CREATE DEFINER=`Stoic Joker`@`%` FUNCTION `MakeUniID`() RETURNS char(7) CHARSET utf8
2. BEGIN
3. declare Result CHAR(7);
4. SET Result = '';
5.  
6. SET Result=CONCAT(
7.         ELT(FLOOR(1 + (RAND() * (26-1))), 'A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z' ),
8.         ELT(FLOOR(1 + (RAND() * (26-1))), 'A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z' ),
9.         ELT(FLOOR(1 + (RAND() * (26-1))), 'A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z' ),
10.         ELT(FLOOR(1 + (RAND() * (10-1))), '0','1','2','3','4','5','6','7','8','9' ),
11.         ELT(FLOOR(1 + (RAND() * (10-1))), '0','1','2','3','4','5','6','7','8','9' ),
12.         ELT(FLOOR(1 + (RAND() * (10-1))), '0','1','2','3','4','5','6','7','8','9' ),
13.         ELT(FLOOR(1 + (RAND() * (10-1))), '0','1','2','3','4','5','6','7','8','9' )
14.         );
15.  
16. RETURN Result;
17. END

^Which I ended up finding here (for anyone else that might end up looking for similar).