增加 T-sql 中的字符值

2021-09-10 00:00:00 sql tsql sql-server

我在一列中有 2 组值，即前 4 个字符是字符，接下来的 4 个字符是数字.例如:AAAA1234现在我必须从右端增加值，即当数值达到 9999 时，我必须将字符增加 1 个字符.

I have 2 set of values in a column i.e first 4 character are characters and next 4 character are numeric. Ex:AAAA1234 Now I have to increment the value from right end i.e when numeric value reached 9999 then I have to increment character by 1 character.

示例:考虑存储在列中的最后一个值是 AAAA9999，然后下一个递增的值应该是序列 AAAB9999,..... AABZ9999,... BZZZ9999..... ZZZZ9999(last value).当它达到 ZZZZ9999 时，我必须将值重置为 AAAA0001.

Sample : Consider the last value stored in a column is AAAA9999 then next incremented values should be in a sequence AAAB9999,....... AABZ9999,..... BZZZ9999..... ZZZZ9999(last value). And when it reaches ZZZZ9999 then I have to reset the value to AAAA0001.

如何在 T-SQL 中做到这一点???

How can do it in T-SQL ???

推荐答案

这是一个概念性脚本，它可以满足您的需求.您需要对其进行调整以满足您的要求

Here is a conceptual script, which does what you want. You will need to tweak it to suit your requirements

DECLARE @test table(TestValue char(8))
DECLARE @CharPart char(4),@NumPart int
SET @CharPart = 'AAAA'
SET @NumPart = 1
WHILE @NumPart <=9999
BEGIN
INSERT INTO @test
SELECT @CharPart+RIGHT(('0000'+CAST(@NumPart AS varchar(4))),4)
IF @NumPart = 9999
    BEGIN
    IF SUBSTRING(@CharPart,4,1)<>'Z'
        BEGIN
        SET @CharPart = LEFT(@CharPart,3)+CHAR(ASCII(SUBSTRING(@CharPart,4,1))+1)
        SET @NumPart = 1
        END
    ELSE IF SUBSTRING(@CharPart,4,1)='Z' AND SUBSTRING(@CharPart,3,1) <>'Z'
        BEGIN
        SET @CharPart = LEFT(@CharPart,2)+CHAR(ASCII(SUBSTRING(@CharPart,3,1))+1)+RIGHT(@CharPart,1)
        SET @NumPart = 1
        END 
    ELSE IF SUBSTRING(@CharPart,3,1)='Z' AND SUBSTRING(@CharPart,2,1) <>'Z'
        BEGIN
        SET @CharPart = LEFT(@CharPart,1)+CHAR(ASCII(SUBSTRING(@CharPart,2,1))+1)+RIGHT(@CharPart,2)
        SET @NumPart = 1
        END 
    ELSE IF SUBSTRING(@CharPart,1,1)<>'Z' 
        BEGIN
        SET @CharPart = CHAR(ASCII(SUBSTRING(@CharPart,1,1))+1)+RIGHT(@CharPart,3)
        SET @NumPart = 1
        END
    ELSE IF SUBSTRING(@CharPart,1,1)='Z' 
        BEGIN
        SET @CharPart = 'AAAA'
        SET @NumPart = 1
        INSERT INTO @test
        SELECT @CharPart+RIGHT(('0000'+CAST(@NumPart AS varchar(4))),4)
        BREAK
        END 
    END
ELSE
    BEGIN
    SET @NumPart=@NumPart+1
    END
END

SELECT * FROM @test

相关文章