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
The best solution is to use
ID INT IDENTITY(1,1)
column to get SQL Server to handle the automatic increment of your numeric valueSo 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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With