Q:

Algorithm for auto generated series number in sql

I want to make an algorithm for generate next series number by specified last series number in sql like below:
     Last Number   Next Number
>      AAAA095       AAAA096
>      AAAA999       AAAB001
>      AAAB001       AAAB002
>      AAAZ999       AABA001

A:

After some try, Finally i got an algorithm & create it to SQL Scalar-valued function for this question as below
CREATE FUNCTION GetNextSeries ( @lastSeriesNo VARCHAR(8))
RETURNS VARCHAR(8)
AS
BEGIN

    DECLARE @nextSeriesNo VARCHAR(8)
    DECLARE @CHAR1 CHAR=SUBSTRING(@lastSeriesNo,1,1)
    DECLARE @CHAR2 CHAR=SUBSTRING(@lastSeriesNo,2,1)
    DECLARE @CHAR3 CHAR=SUBSTRING(@lastSeriesNo,3,1)
    DECLARE @CHAR4 CHAR=SUBSTRING(@lastSeriesNo,4,1)
    DECLARE @n INT=SUBSTRING(@lastSeriesNo,5,3)

    SET @n = @n + 1
    IF(@n>999)
    BEGIN
        SET @n=1
        IF(@CHAR4<>'Z')
        BEGIN
            SET @CHAR4=CHAR(UNICODE(@CHAR4)+1)
        END
        ELSE IF(@CHAR3<>'Z')
        BEGIN
            SET @CHAR4='A'
            SET @CHAR3=CHAR(UNICODE(@CHAR3)+1)
        END
        ELSE IF(@CHAR2<>'Z')
        BEGIN
            SET @CHAR4='A'
            SET @CHAR3='A'
            SET @CHAR2=CHAR(UNICODE(@CHAR2)+1)
        END
        ELSE IF(@CHAR1<>'Z')
        BEGIN
            SET @CHAR4='A'
            SET @CHAR3='A'
            SET @CHAR2='A'
            SET @CHAR1=CHAR(UNICODE(@CHAR1)+1)
        END
    END

    SET @nextSeriesNo=@CHAR1+@CHAR2+@CHAR3+@CHAR4+(CASE LEN(@n) WHEN 1 THEN '00' WHEN 2 THEN '0' ELSE '' END)+convert(VARCHAR(3),@n) 
    RETURN @nextSeriesNo
END

