Formatting an auto increment

Question

Formatting an auto increment

ijesri228

Valued Member

Points: 51
More actions
March 17, 2018 at 1:54 pm

#343976

Hello,
I am creating a program using VB.NET. I would like the database to handle automatically creating a number for orders and customers/vendors using the following format.
Vendors - "V-001000"
Customers - "C-001000"
Purchase Orders - "PO-001000"
Sales Orders - "SO-001000"
Is it possible to have it done? I am using SQL database on Azure.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

yes, with a calculated, persisted column, that uses the real identity column.
Note this is just creating a formatted version of your identity, so it's a little repetitive.
You might be able to do something similar with a single column, if it featured a SEQUENCE and the calcualted function.
Also, your specific design of 7 characters limits the number of items you can have, which would be less than the max size of an int column.

IF OBJECT_ID('[dbo].[Vendor]') IS NOT NULL DROP TABLE [dbo].[Vendor] GO CREATE TABLE [dbo].[Vendor] ( [VendorID] AS ('V-'+right('0000000'+CONVERT([varchar](10),[VID]),(9))) PERSISTED, [VID] INT IDENTITY(1000,1) NOT NULL, [VendorName] VARCHAR(50) NOT NULL, [OtherColumns] VARCHAR(30) NULL, CONSTRAINT [PK__Vendor__VendorID] PRIMARY KEY NONCLUSTERED ([VID] asc) )

CREATE CLUSTERED INDEX IX_Vendor_VendorID ON [Vendor]([VendorID])

insert into Vendor(VendorName,OtherColumns) SELECT 'Google','SomeStuff' UNION ALL SELECT 'Amazon','otherStuff'

SELECT * FROM Vendor

/* V-000001000 1000 Google SomeStuff V-000001001 1001 Amazon otherStuff */

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!