4位编码都在[0-9][A-Z]的范围里生成,每一位都有36种变化可能。纯数字的简单,字母组合的按照以下写法,只能生成A001-ZZZZ范围内的编码,希望可以同时也生成如以下格式组合的编码0A01,0A02,....,0A99,....,0A01,0A02,....,0A99,....,
001A,002A,....,999A,....001Z,002Z,....,999Z,需要怎么修改才可以呢?
USE tempdb
GO
CREATE TABLE #Tmp1
(
ID INT IDENTITY(1,1),
samplecode VARCHAR(10)
)
begin
DECLARE @CurrentCode VARCHAR(10)
SELECT @CurrentCode=MAX(samplecode)
FROM #Tmp1
if(@CurrentCode is null) set @CurrentCode='A000'
SELECT @CurrentCode= CASE WHEN RIGHT(@CurrentCode,3)='999' THEN CHAR(ASCII(LEFT(@CurrentCode,1))+1)+'0001'
ELSE LEFT(@CurrentCode,1) + RIGHT(REPLICATE('0',3) + RTRIM(CAST(STUFF(@CurrentCode,1,1,'') AS INT)+1),3) END
INSERT INTO #Tmp1
( samplecode )
VALUES ( @CurrentCode)
end