want 7 digit string which is incremented automatically

Question

1.00/5 (1 vote)

See more:

hi,

for my banking application ,

i want to generate a string of 7 charecters in which first two are given by user

and the remaining 5 charecters will be generated as A0001
A0002
'
'
'
A9999
B0001
B0002
'
'
'
B9999

C0001
C0002
'
'
'
C9999

Z0001
Z0002
'
'
'
Z9999

initially starts with A and 0001 and ends with A and 9999
like B and 0001 and ends with B and 9999
and follow it upto Z9999

Posted 15-Aug-12 20:57pm

sk. maqdoom ali

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

StianSandberg · Accepted Answer · 2012-08-15T21:50:00

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

SQL

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:
(See comments for explanation)

SQL

-- 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.