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;

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;

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;