Other Software > Developer's Corner
Issue with MySQL db Function and rand()
(1/1)
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 ---CREATE DEFINER=`Stoic Joker`@`%` FUNCTION `MakeUniID`() RETURNS char(7) CHARSET utf8BEGINdeclare Result CHAR(7);SET Result = ''; SET Result=CONCAT( substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ', rand(@seed:=round(rand(@seed)*4294967296))*26+1, 1), substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ', rand(@seed:=round(rand(@seed)*4294967296))*26+1, 1), substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ', rand(@seed:=round(rand(@seed)*4294967296))*26+1, 1), substring('0123456789', rand(@seed:=round(rand(@seed)*4294967296))*10+1, 1), substring('0123456789', rand(@seed:=round(rand(@seed)*4294967296))*10+1, 1), substring('0123456789', rand(@seed:=round(rand(@seed)*4294967296))*10+1, 1), substring('ABCDEFGHIJ', rand(@seed)*10+1, 1) ); RETURN Result;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 ---CREATE DEFINER=`Stoic Joker`@`%` FUNCTION `MakeUniID`() RETURNS char(7) CHARSET utf8BEGINdeclare Result CHAR(7);SET Result = ''; SET Result=CONCAT( 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' ), 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' ), 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' ), ELT(FLOOR(1 + (RAND() * (10-1))), '0','1','2','3','4','5','6','7','8','9' ), ELT(FLOOR(1 + (RAND() * (10-1))), '0','1','2','3','4','5','6','7','8','9' ), ELT(FLOOR(1 + (RAND() * (10-1))), '0','1','2','3','4','5','6','7','8','9' ), ELT(FLOOR(1 + (RAND() * (10-1))), '0','1','2','3','4','5','6','7','8','9' ) ); RETURN Result;END
^Which I ended up finding here (for anyone else that might end up looking for similar).
Navigation
[0] Message Index
Go to full version