3

How to Auto Increment ID Numbers with Letters and Numbers, example "KP-0001" it will increment to "KP-0002"

Thank you!

John Woo
  • 258,903
  • 69
  • 498
  • 492
user2038163
  • 39
  • 1
  • 1
  • 5

6 Answers6

5

here is a useful article

But basically I encourage you to create your own algorithm on this. You can add that algorithm in BEFORE INSERT trigger. Or you can do that on the front-end.

Example of pseudocode for the algorthm

  • get the lastID [KP-0001]
  • remove some characters and put it in a variable [KP-]
  • convert the remaining into number since it's a string [0001]
  • increment by 1 [1 + 1 = 2]
  • convert it back to string and pad zero on the right [0002]
  • concatenate the variable and the newly incremented number [KP-0002]
  • save it.
Community
  • 1
  • 1
John Woo
  • 258,903
  • 69
  • 498
  • 492
2

I tried to do that in many ways but was unable to reach the solution... I also used triggers but that too didn't help me...

But I found a quick solution for that...

For example you want your employee to have employee codes 'emp101', 'emp102',...etc. that too with an auto increment...

First of all create a table with three fields the first field containing the letters you want to have at the beginning i.e."emp", the second field containing the auto increasing numbers i.e 101,102,..etc., the third field containing both i.e 'emp101', 'emp102',...etc.

CREATE TABLE employee
(
empstr varchar( 5 ) default 'emp',
empno int( 5 ) AUTO_INCREMENT PRIMARY KEY ,
empcode varchar( 10 )
);

now providing an auto_increment value to empno. 

ALTER TABLE employee AUTO_INCREMENT=101;

now coming to the topic... each time you insert values you have to concatenate the first two fields to get the values for the third field 

INSERT INTO employee( empcode )
VALUES ('xyz');
UPDATE employee SET empcode = concat( empstr, empno ) ;
Ömer An
  • 600
  • 5
  • 16
user3151681
  • 45
  • 11
1

You can't auto increment varchar data type. Other way of doing this is to bifurcate varchar column into two different columns one will have integer part and other will have alphabet like in your case KP- once you auto increment all integer rows just concatenate these two columns

Raul
  • 579
  • 1
  • 5
  • 17
1
    
CREATE TABLE Customer (
        CUSId INT NOT NULL IDENTITY(1, 1) PRIMARY KEY
        ,CUSKey AS 'Cus' + RIGHT('000' + CONVERT(VARCHAR(5), CUSId), 6) PERSISTED
        ,CusName VARCHAR(50)
        ,mobileno INT
        ,Gender VARCHAR(10)
        )
Juned Khan
  • 11
  • 1
  • Thank you for this code snippet, which might provide some limited short-term help. A proper explanation [would greatly improve](//meta.stackexchange.com/q/114762) its long-term value by showing *why* this is a good solution to the problem, and would make it more useful to future readers with other, similar questions. Please [edit] your answer to add some explanation, including the assumptions you've made. – Toby Speight Apr 18 '18 at 11:58
0

Auto-increment is an integer, so adding text will not be possible.

Check out this question for other references.

Community
  • 1
  • 1
Black Bird
  • 797
  • 1
  • 10
  • 34
0

Make a procedure, in my case MySQL.

CREATE PROCEDURE MOSTRAR_CODIGO_CLASE_PRODUCTO()
BEGIN
    declare max varchar(10);
    declare num int;
    declare CCodigo varchar(10);
    
    set max = (select MAX(Codigo_CP) from CLASE_PRODUCTO);
    set num = (SELECT LTRIM(RIGHT(max,4)));
    if num>=1 and num <=8 then
        set num = num + 1;
        set CCodigo = (select concat('CP000'  ,  CAST(num as CHAR)));
    elseif num>=9 and num <=98 then
        set num = num + 1;
        set CCodigo = (select concat('CP00'  ,  CAST(num as CHAR)));
    elseif num>=99 and num <=998 then
        set num = num + 1;
        set CCodigo = (select concat('CP0'  ,  CAST(num as CHAR)));
    elseif num>=999 and num <=9998 then
        set num = num + 1;
        set CCodigo = (select concat('CP'  ,  CAST(num as CHAR)));
    else 
        set CCodigo=(select 'CP0001');
    end if;
    
    SELECT MAX(CCodigo) AS Codigo_CP FROM CLASE_PRODUCTO;
END $


Java Class

public static boolean insertarClaseProducto(ClaseP cp){
        boolean resp = false;
        
        Connection cn;
        Connection con = new Connection();
        cn = con.connect();              
        
        try{
            CallableStatement cs = cn.prepareCall("CALL REGISTRAR_CLASE_PRODUCTO (?)");
            cs.setString(1, cp.getNombreCP());
            int i = cs.executeUpdate();
            
            if(i==1)
                resp = true;
            else
                resp = false;
            
        }catch(Exception e){System.out.println(e);}
        return resp;
    }

returns:

Codigo_MP   Nombre_MP    Estado_MP
MP0001      LG           HAB
MP0002      GENIUS       HAB
MP0003      MICRONICS    HAB
MP0004      SONY         HAB
MP0005      PANASONIC    HAB
Marcel Motta
  • 43
  • 5