CREATE FUNCTION [dbo].[FUN_GETNEXTNO](@str NVARCHAR(4000),@length INT)
RETURNS NVARCHAR(4000)
AS
BEGIN
IF ISNULL(@str,'') = ''
RETURN right('0000000000'+'1',@length)
DECLARE @returnChar NVARCHAR(4) --返回字符
SET @returnChar = ''
DECLARE @Position INT
SET @Position = LEN(@str)
DECLARE @needUp BIT
SET @needUp = 1
WHILE @Position > 0
BEGIN
DECLARE @charAsc INT --当前字符ASC码
SET @charAsc = ASCII(SUBSTRING(@str,@Position,1))
IF @needUp = 0
BEGIN
SET @returnChar = @returnChar + CHAR(@charAsc)
SET @Position = @Position - 1
CONTINUE
END
IF @charAsc = 57
BEGIN
--当字符为9的时候,则升为'A' ASCII 65
SET @charAsc = 65
SET @needUp = 0
END
ELSE IF @charAsc = 90
BEGIN
--当字符为Z的时候,则将当前位数设置为0 ASCII 48,需要升位
SET @charAsc = 48
SET @needUp = 1
END
ELSE
BEGIN
SET @charAsc = @charAsc + 1
SET @needUp = 0
END
SET @Position = @Position - 1
SET @returnChar = @returnChar + CHAR(@charAsc)
END
RETURN REVERSE(@returnChar)
END
调用:
SELECT dbo.FUN_GETNEXTNO('02ZH',4)