How do I auto-increment a alphanumeric string primary key?

Let’s say I have table A with an alphanumeric string primary key. The code used to create the table and how the table looks are shown below.

CREATE TABLE A 
(
    ID CHAR(7) NOT NULL,
    ...
    CONSTRAINT PK_A PRIMARY KEY (ID)
)

| ID       | ...      |
| -------- | -------- |
| C000001  | ...      |
| C000002  | ...      |

I want to insert a new row into Table A and I don’t want to type out C000003 or C000004 every time. Is there a way to auto increment this?

I have thought about getting the latest id using

select top 1 CustId 
from Customer 
order by CustId desc

For splitting, I used SUBSTRING(ID, 2, 7). For joining back, I can use concat('C', ID + 1).

The issue is, if I add one to the numeric portion, it would give me 3 instead of 000003. Is there a way to save the 0’s?

I just need help with incrementing.

My current code is like this:

declare @lastid varchar(7), @newID varchar(7)
set @lastid = (select top 1 ID from A order by ID desc)
set @newID = SUBSTRING(@lastid, 2, 7)
select CONCAT('C', @newID + 1) -- need help with the increment

Any help appreciated

EDIT 1: If the numbers are less than 10 (ie one digit), I can manually add in 0’s to fill up the gaps. But if the number has 2 digits or more, I can’t do that so I’m thinking of a solution for this.

Answer

One word of advice: DON’T DO THIS! Using this SELECT MAX() + 1 approach is not safe under load, as soon as more than one user will be using your application, you WILL HAVE DUPLICATES – sooner or later.

The only viable solution is to use

• an ID INT IDENTITY(1,1) column to get SQL Server to handle the automatic increment of your numeric value
• a computed, persisted column to convert that numeric value to the value you need

So try this:

CREATE TABLE dbo.Customer
(
    ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
    CustomerID AS 'C' + RIGHT('000000' + CAST(ID AS VARCHAR(6)), 6) PERSISTED,
    -- .... your other columns here....
)
  

Now, every time you insert a row into Customer without specifying values for ID or CustomerID:

INSERT INTO dbo.Customer(Col1, Col2, ..., ColN)
VALUES (Val1, Val2, ....., ValN)

then SQL Server will automatically and safely increase your ID value, and CustomerID will contain values like C000001, C000002,…… and so on – automatically, safely, reliably, no duplicates.