Open Source - how to generate alpha numeric id in mysql database

my alpha numeric id is in the format like:
A116
A117
A118
A119
....

...

A5000
B50001

plz help me..

it is very urgent...

Try this one:

CREATE FUNCTION NEW_ID ()
RETURNS varchar(50)
AS
BEGIN

declare @s as varchar(50)
declare @exist as bit
set @exist = 1
while (@exist = 1)
begin
set @s = Char(round(Rand()*25+65,0))
set @s =@s+ Char(round(Rand()*25+65,0))
set @s =@s+ Char(round(Rand()*25+65,0))
set @s =@s+ Char(round(Rand()*25+65,0))
set @s =@s+ Char(round(Rand()*25+65,0))

--check in the table for existence of new ID
select  @exist = count(*) MyTable where MyID = @s
end
RETURN @s
END
GO

replace  MyTabl,MyID.....

CREATE TABLE `myseq` (                 
          `id` varchar(10) default NULL,       
          `date` date default NULL             
        ) ENGINE=MyISAM DEFAULT CHARSET=latin1 


# Function which generate unique number

DELIMITER |
DROP FUNCTION IF EXISTS `uGetSeq`|
CREATE FUNCTION uGetSeq()
    RETURNS VARCHAR(20)
READS SQL DATA
BEGIN

DECLARE intCount int default 0;
DECLARE dtDate date;
DECLARE chString int default 0;


SELECT count(1) INTO intCount
  FROM myseq
WHERE date=current_date();

SELECT max(date) INTO dtDate
  FROM myseq;

    IF intCount = 0 THEN
            
        RETURN('ab00001');

    ELSEIF intCount > 0 THEN

SELECT MAX(SUBSTRING(id,3)+0) INTO chString
          FROM myseq
         WHERE date=dtDate;
       
        SET @midString = chString + 1;
       
        IF LENGTH(@midString) = 4 THEN

RETURN(CONCAT('ab','0',@midString));

        ELSEIF LENGTH(@midString) = 3 THEN

RETURN(CONCAT('ab','00',@midString));

        ELSEIF LENGTH(@midString) = 2 THEN

RETURN(CONCAT('ab','000',@midString));

        ELSEIF LENGTH(@midString) = 1 THEN

RETURN(CONCAT('ab','0000',@midString));
        END IF;

    END IF;

END;

|

### How to check...

SELECT uGetSeq();

### You can directly use this function at the time of insert

insert into myseq values(uGetSeq(),current_date());


select * from myseq;

ab00001 2025-11-30
ab00002 2025-11-30
ab00003 2025-11-30
ab00001 2025-12-01
ab00002 2025-12-01
ab00003 2025-12-01
ab00004 2025-12-01
ab00005 2025-12-01