Want 5 digit string which is incremented automatic (A0001 - Z9999)

The first thing you need is a table to hold your generated keys:

CREATE TABLE [dbo].[IdGenerator](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Pk] [varchar](5) NOT NULL
)
 
-- Insert the first key
INSERT INTO IdGenerator (Pk) VALUES ('A0001')

It contains 2 columns. Id (the primary key, used to order your keys) and the key you want!

Then you need this script to generate a new Key base on the previous one:

-- Declare some variables we need
DECLARE @PK varchar(5)
DECLARE @Character char(1)
DECLARE @Number int
 
-- Get the last generated key (I'll call it @PK)
SET @PK = (SELECT TOP 1 PK FROM IdGenerator Order by Id desc)
-- Extract the character (A,B,C ...)
SET @Character = (SELECT SUBSTRING (@PK ,1, 1))
-- Extract the number (4 last characters. "0001", "0002"....)
SET @Number = (SELECT SUBSTRING (@PK ,2, 4))
 
-- Increase the number by 1
SET @Number = @Number + 1
 
-- Check if the number has reached the top (9999)
IF @Number > 9999
BEGIN
   -- If it has, set it to 1
   SET @Number = 1
   -- And find the next character in the alphabet (A->B, B->C...)
   SET @Character = char(ascii(@Character)+1)
END
 
-- Pad your number (1->0001, 2->0002...) and set you newly generated key
SET @PK = @Character + REPLACE(STR(@Number, 4), SPACE(1), '0')
 
-- Insert it to the IdGenerator table so we can use it for the next key
INSERT INTO IdGenerator (PK) VALUES (@PK)
 
-- And heres your new unique key :)
SELECT @PK

For this to work, you will have to add the first value (A0001) manually. And you
You can put this code in a stored procedure.


Reference : http://www.codeproject.com/Questions/441365/want-7-digit-string-which-is-incremented-automatic