How to Auto Increment ID Numbers with Letters and Numbers

Question

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

Thank you!

Answer 1

here is a useful article

• auto increment with a string of numbers and letters

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.

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