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.

• Lowell

  SSC Guru

  Points: 323522

  More actions

  March 18, 2018 at 8:14 am

  #1983709

  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!