想要7位数字的字符串，该字符串会自动递增

问题描述

我的银行业务申请，

我想生成一个由7个字符组成的字符串，其中前两个由用户给出

其余5个字符将生成为A0001
A0002
''
''
''
A9999
B0001
B0002
''
''
''
B9999

C0001
C0002
''
''
''
C9999

Z0001
Z0002
''
''
''
Z9999

最初以A和0001开始，以A和9999结尾
类似于B和0001，并以B和9999结尾
并遵循它直到Z9999

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

推荐答案

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')

它包含2列. ID(主键，用于订购键)和所需的键！

然后，您需要此脚本在上一个脚本的基础上生成新的Key:
(请参阅注释以获取解释)

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)

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

为此，您必须手动添加第一个值(A0001).而你
您可以将此代码放入存储过程中.

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.

这篇关于想要7位数字的字符串，该字符串会自动递增的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！