Mysql function to genarate custom ids

Question

I need some help in creating a MySQL function

This function generates a user id for my user, Which generates 5 digits unique id starting from A0001, A0002, B0001, C0001, and so on but the problem is it reaches F9999 as per my function the following number should be G0000

But my requirement is can't go past letter F We can't have a user id that is more than 5 'digits' and we can only use the letters A to F

Se I come with some Solution moving on to a range that is something like this: AA000, AA001, AA002.... and then AB000, AB001, AB002, AF999 BA000, etc.

This is my current function which I use to generate userid

DELIMITER $$
CREATE DEFINER=`root`@`localhost` FUNCTION `getNextID`() RETURNS varchar(10) CHARSET utf8
BEGIN
set @prefix := (select COALESCE(max(left(id, 1)), 'A') from users where left(id, 1) < 1);
set @highest := (select max(CAST(right(id, 4) AS UNSIGNED))+1 from users where left(id, 1) = @prefix);
if @highest > 9999 then
    set @prefix := CHAR(ORD(@prefix)+1);
    set @highest := 0;
end if;
RETURN concat( @prefix , LPAD( @highest, 4, 0 ) );
END$$
DELIMITER ;

You realize that this change makes your system handle _fewer_ user ids, right? The first id method allows for 60,000 distinct user ids. The second method allows for only 36,000. — Bill Karwin, Sep 16 '22 at 17:29
I think I misunderstood your question and gave a wrong answer (deleted it). So, basically, you want to use a hexadecimal number of length 5 and the problem is, that you already have assigned values 'A0000' to 'F9999' while not having used 'A' to 'F' in the lower four digits? — Mihe, Sep 16 '22 at 18:05
@BillKarwin Currently i used this method but my current user id is F5966 after few days it will switch to G series and we don't need that we want a hexadecimal number of length 5 — Md Hasibur Rahaman, Sep 17 '22 at 02:15
@Mihe Yes, you are right as we are close to completing the F series we have to use it fixed fir 2 letters like AA000 AA001 . AB000, AB002 so on can you please help me on that — Md Hasibur Rahaman, Sep 17 '22 at 02:17

Mihe · Accepted Answer · 2022-09-17T07:57:47.563

Your ID can be thought of a hexadecimal number consisting of letters only, followed by a decimal number. Each hexadecimal digit starts a new series of decimal numbers, because the ID is of fixed length 5.

The first subproblem is to find the maximum ID, because it should be assumed that F9998 < F9999 < AA000 < AA001. We can calculate H*10000 + D with H being the hexadecimal part and D the decimal part of the ID to get the right order.

SELECT id
FROM (
    SELECT 'AB999' as id UNION 
    SELECT 'AA000' UNION
    SELECT 'F9999' UNION
    SELECT 'AAA00' UNION
    SELECT 'FFFF9' UNION
    SELECT 'FFFF8' UNION
    SELECT 'FFFD3') user
ORDER BY conv(regexp_substr(id, '^[A-F]*'), 16, 10) * 10000 + CAST(substring(id, length(regexp_substr(id, '^[A-F]*')) + 1) AS unsigned) DESC
LIMIT 1;

The second subproblem is to find the successor of a given ID. We calculate the decimal number like above but use the correct factor (10^n with n being the length of the decimal part) this time, then we add one to this number and convert it back to the hex/dec representation. In the hexadecimal part there may be 0s and 1s which have to be replaced by 'A'. Whenever the hex part gets longer, the decimal part consists of 0s only. That is, we can just return a substring of the desired length and strip trailing 0es:

DELIMITER //
CREATE FUNCTION nextId(id VARCHAR(5)) RETURNS VARCHAR(5) NO SQL
BEGIN
  set @hexStr := regexp_substr(id, '^[A-F]*');
  set @digits := length(id) - length(@hexStr);
  set @decimalPart := CAST(right(id, @digits) AS UNSIGNED);
  set @factor := pow(10, @digits);
  set @hexPart := conv(@hexStr, 16, 10);
  set @n := @hexPart * @factor + @decimalPart + 1; -- ID increased by 1
  set @decimalPart := mod(@n, @factor);
  set @hexStr := regexp_replace(conv(floor(@n / @factor), 10, 16), '[01]', 'A');
  return substring(concat(@hexStr, lpad(@decimalPart, @digits, '0')), 1, length(id));
END;
//
DELIMITER ;

Using this function

SELECT id, nextId(id) next_id
FROM (
    SELECT 'F9998' as id UNION
    SELECT 'F9999' UNION
    SELECT 'AA999' as id UNION 
    SELECT 'AB000' UNION
    SELECT 'AB999' UNION
    SELECT 'AF999' UNION
    SELECT 'FF999' UNION
    SELECT 'AAA00') user;

results in

id	next_id
F9998	F9999
F9999	AA000
AA999	AB000
AB000	AB001
AB999	AC000
AF999	BA000
FF999	AAA00
AAA00	AAA01

Here's a fiddle

Mysql function to genarate custom ids

1 Answers1