MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select version();


version()


8.0.30


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;


id


FFFF9


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;
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;



id
next_id


F9998
F9999

F9999
AA000

AA999
AB000

AB000
AB001

AB999
AC000

AF999
BA000

FF999
AAA00

AAA00
AAA01

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