Auto generate function in SQL Server 2000

Question

I have a table named 'Customer'.

This table contains a unique Identification field for each customer named 'CustomerNo'. It should be in the format : First an alphabet Starting from A-Z.

Then a four digit number starting from 1-9999 and the next is a three digit number starting from 1-999.This is a unique field.So it shoudn't repeat in the table Customer.

E.g: A1000-100.

Is there any function to generate this format?

Table structure
-----------------------
CustomerID         CustomerName          CustomerNo

1                 John                   A1000-100
2                 Sajan                  A1001-100

Answer 1

You could just use a normal auto incrementing identity column and the following formula (possibly in a derived column) to map this to display format.

CHAR((id-1)/9999/999 + 65) + 
RIGHT('0000' + CAST(1 + (id-1)%(999*9999)/999 AS VARCHAR(4)),4) + 
'-' + 
RIGHT('000' + CAST(1 + (id-1)%999 AS VARCHAR(3)),3)

This avoids the need for you to have to deal with any concurrency issues yourself.