topbanner_forum
  *

avatar image

Welcome, Guest. Please login or register.
Did you miss your activation email?

Login with username, password and session length
  • Thursday March 28, 2024, 4:27 am
  • Proudly celebrating 15+ years online.
  • Donate now to become a lifetime supporting member of the site and get a non-expiring license key for all of our programs.
  • donate

Author Topic: Issue with MySQL db Function and rand()  (Read 2994 times)

Stoic Joker

  • Honorary Member
  • Joined in 2008
  • **
  • Posts: 6,646
    • View Profile
    • Donate to Member
Issue with MySQL db Function and rand()
« on: February 10, 2015, 03:40 PM »
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

  • Honorary Member
  • Joined in 2008
  • **
  • Posts: 6,646
    • View Profile
    • Donate to Member
Re: Issue with MySQL db Function and rand()
« Reply #1 on: February 11, 2015, 07:13 AM »
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).