Clever Geek Handbook

Create a column that grows from the last index

I need to create a column for InvoiceID. and I want to store the formula of this column like this

INV0000001,
INV0000002,
.
.
.
.
INV0000010,
INV0000011,
.
. and so on.

As you can see, this column is incremented with the last index. How can i do this.

I am using SQL Server 2012.

I searched but couldn't find how to increase a number like this.

+3


source to share


3 answers


Try using computed column

MSDN

CREATE TABLE Yourtablename
(
    ID int IDENTITY (1,1) NOT NULL,
    InvoiceID AS 'INV'+ right('000000'+cast(ID as varchar(20)),7) PERSISTED
);


SQLFIDDLE DEMO

For more information on why you should make your computed column like persisted

here

+6


source


try this one: - (fully dynamic)



Declare @NextInvoice    Int = 0

Select  @NextInvoice = Isnull(Max(Cast(Replace('INV0000011','INV','') As Bigint)),0) + 1

Select  'INV' + Left('0000000', (Len('0000000') -Len(@NextInvoice))) + Cast(@NextInvoice As Varchar(20))
+1


source


hope this works for u

SELECT
    INV + right('000' + an_auto_increment_column, 3)  AS InvoiceID
    FROM yourTable
0


source






More articles:


All Articles