自动增量字母数字ID

Question

希望我能在这里得到一些帮助。我有以下存储过程，它将通过插入@NAME中的第一个字母表来生成字母数字ID。即@name = Test将生成。T001。但我正在尝试包含前两个aplha..也就是要生成的@name = Test。TE001。我尝试过更改@PREFIX VARCHAR(2)和..SUBSTRING(@NAME, 1,2)，但是当我这样做的时候。数字不会自动增加。

DECLARE @NEWID VARCHAR(5);
DECLARE @PREFIX VARCHAR(1);
SET @PREFIX = UPPER(SUBSTRING(@NAME, 1, 1))
SELECT @NEWID = (@PREFIX + replicate('0', 3 - len(CONVERT(VARCHAR,N.OID + 1))) +       CONVERT(VARCHAR,N.OID + 1)) FROM (
SELECT CASE WHEN MAX(T.TID) IS null then 0 else MAX(T.TID) end as OID FROM (
SELECT SUBSTRING(ID, 1, 1) as PRE_FIX,SUBSTRING(ID, 2, LEN(ID)) as TID FROM Testing
) AS T WHERE T.PRE_FIX = @PREFIX
) AS N

Answer 1

我认为这应该行得通：

DECLARE @NEWID VARCHAR(5);
DECLARE @PREFIX VARCHAR(2);
SET @PREFIX = UPPER(SUBSTRING(@NAME, 1, 2))
SELECT @NEWID = (@PREFIX + replicate('0', 3 - len(CONVERT(VARCHAR,N.OID + 1))) +       CONVERT(VARCHAR,N.OID + 1)) FROM (
SELECT CASE WHEN MAX(T.TID) IS null then 0 else MAX(T.TID) end as OID FROM (
SELECT SUBSTRING(ID, 1, 2) as PRE_FIX,SUBSTRING(ID, 2, LEN(ID)) as TID FROM Testing
) AS T WHERE T.PRE_FIX = @PREFIX
)

Answer 2

尝尝这个

DECLARE @NEWID VARCHAR(5);
DECLARE @PREFIX VARCHAR(2);
SET @PREFIX = UPPER(SUBSTRING(@NAME, 1, 2))
SELECT @NEWID = (@PREFIX + REPLICATE('0', 3 - LEN(CONVERT(VARCHAR, N.OID + 1))) + CONVERT(VARCHAR,N.OID + 1)) 
FROM (
      SELECT CASE WHEN MAX(T.TID) IS NULL
                  THEN 0
                  ELSE MAX(T.TID) END AS OID 
      FROM (
            SELECT SUBSTRING(ID, 1, 2) AS PRE_FIX, SUBSTRING(ID, 3, LEN(ID)) AS TID 
            FROM Testing
            ) AS T 
      WHERE T.PRE_FIX = @PREFIX
      ) AS N

上的演示