CODEWITHSUNDEEP
sqlsql-serverselect
Arif YILMAZ
Arif YILMAZ

Reputation: 5866

SQL Server - Create a custom auto-increment field

I am trying to produce a custom auto-increment functionality in sql. my custom auto-incerement ID should be like below...

S1501.001

"S" is for Supplier's name first letter.

"15" is for this year's last 2 digits.

"01" is today's month

"." will always be there

"001" is my incrementer.

the counter will go on like below

S1501.001
S1501.002
S1501.003
S1501.004

Firstly, I have to find the "S1501." and find the ID with highest digits at the end. I can create a new "S1501.005". How can I do this?

I have done something but didnt work.

SELECT TOP 1 (SELECT SUBSTRING('S1501.001', 7,3)),* 
FROM LG_001_01_SERILOTN 
WHERE  CODE LIKE SUBSTRING('S1501.001', 1,6)+'%'
ORDER BY (SELECT SUBSTRING('S1501.001', 7,3)) DESC

Upvotes: 4

Views: 10616

Answers (1)

marc_s
marc_s

Reputation: 754268

The best 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.tblCompany
  (ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
   CompanyID AS 'S1501.' + RIGHT('000' + CAST(ID AS VARCHAR(3)), 3) PERSISTED,
   .... your other columns here....
  )

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

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

then SQL Server will automatically and safely increase your ID value, and CompanyID will contain values like S1501.001, S1501.002,...... and so on - automatically, safely, reliably, no duplicates.

Upvotes: 7

Related Questions