Click here to Skip to main content
14,976,254 members
Please Sign up or sign in to vote.
4.00/5 (1 vote)
See more:
Hi All,

In my project I want to create a auto increment ID, like A0001, A0002...

I know the auto increment is possible in mysql. but is it possible Character Auto Increment in Mysql?.

Thanks in advance!.
Posted
Updated 6-Jun-11 19:40pm
v2

No you can not.
But you can create an auto increment that is zero filled.
SQL
CREATE TABLE mytbl (
  id int(5) unsigned zerofill NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (id)
);


This will make id like:
00001
00002
00003
...
   
v2
Comments
Sagotharan Jagadeeswaran 9-Jun-11 0:40am
   
Thank You!. I done that with help of C#.
Kim Togo 9-Jun-11 2:38am
   
You are welcome :-)
Hope this[^] might help you.
   
SQL
AS
BEGIN
BEGIN TRANSACTION
    IF NOT EXISTS (SELECT 1 FROM Item_Master where Item_Code = @Item_Code)
    BEGIN
    DECLARE @ItemCode VARCHAR(10)
    SET @ItemCode =(SELECT RIGHT(MAX(CONVERT(VARCHAR(8),Item_Code)),6) +1 FROM Item_Master)
    IF(@ItemCode IS NULL)
    BEGIN
    SET @ItemCode = '000001'
    end
    ELSE
    BEGIN
        if LEN(@ItemCode) = 1
        BEGIN
            set @ItemCode = '00000' + @ItemCode
        end
        else if LEN(@ItemCode) = 2
        BEGIN
            set @ItemCode = '0000' + @ItemCode
        end
        else if LEN(@ItemCode) = 3
        BEGIN
            set @ItemCode = '000' + @ItemCode
        end
        else if LEN(@ItemCode) = 4
        BEGIN
            set @ItemCode = '00' + @ItemCode
        end
        else if LEN(@ItemCode) = 5
        BEGIN
            set @ItemCode = '0' + @ItemCode
        end
        else if LEN(@ItemCode) = 6
        BEGIN
            set @ItemCode = @ItemCode
        end
    end
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900