How to Auto Increment ID Numbers with Letters and

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

Answer 3

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

Answer 4

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

Answer 5

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

Check out this question for other references.