問題描述

我在一列中有 2 組值，即前 4 個字符是字符，接下來的 4 個字符是數(shù)字.例如:AAAA1234現(xiàn)在我必須從右端增加值，即當數(shù)值達到 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 ???

推薦答案

這是一個概念性腳本，它可以滿足您的需求.您需要對其進行調(diào)整以滿足您的要求

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

這篇關于增加 T-sql 中的字符值的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！