ATTENTION: You are viewing a page formatted for mobile devices; to view the full web page, click HERE.

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