Sequential Increment in Alphanumeric Invoice Number using MSSQL

Question

i have a table named as sales. In this table i have a column Invoice_Number. What i want to achieve is to get invoice number in alphanumeric sequential order i.e. one Static Letter and then a number that changes just like A1,A2,A3... and so on. i don't want to change A to B as i saw in rest of the examples.

I can get the numeric increment by this query:

Select IsNull(MAX([Invoice_Number] + 1), 1) from sales

But it cannot add an alphabet prior to it.

Thanks.

You do not want to do this to yourself. You are creating a race condition. Why do you have to have an 'A' at the front of your invoice number? Does it really matter if it is stored in the database with an A? Couldn't you just stick that letter on for invoices and other communication? Or use a computed column to append the A to the front. — Sean Lange
– Sean Lange, Commented Oct 18, 2017 at 15:41

S.H · Accepted Answer · 2017-10-18 15:40:46Z

1

Try using a cast like so to return a varchar, let me know if this helps :).

SELECT 'A' + CAST(ISNULL(MAX([Invoice_Number] + 1), 1) AS nvarchar) FROM sales

answered Oct 18, 2017 at 15:40

S.H

111 silver badge5 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

David Rushton · Accepted Answer · 2017-10-18 15:42:07Z

1

You could use your existing identity column as a seed. Combine this with a computed column to return the InvoiceNumber.

-- Combine a seed with a computed column.
CREATE TABLE #Example
(
    Seed            INT IDENTITY(1,1),
    InvoiceNumber   AS 'A' + CAST(Seed AS VARCHAR(5)),
    Customer        VARCHAR(25)
);

-- Add some sample records.
INSERT INTO #Example
(
    Customer
)
VALUES

    ('A PLC'),
    ('B PLC'),
    ('C LTD')
;

-- Returns InvoiceNumbers A1, A2 & A3.
SELECT
    *
FROM
    #Example
;

answered Oct 18, 2017 at 15:42

David Rushton

5,0501 gold badge19 silver badges32 bronze badges

1 Comment

Reno Over a year ago

Or you can create a separate sequence if you want to avoid holes in your invoice numbering/ e.g. when a draft record was deleted. CREATE SEQUENCE InvoiceCounter START WITH 1 INCREMENT BY 1 ; GO select 'A'+cast(next value for InvoiceCounter as nvarchar )

Collectives™ on Stack Overflow

Sequential Increment in Alphanumeric Invoice Number using MSSQL

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related