declare @code varchar(100) set @code='AAA99999' --set @code='AAZ99999'--拆分 declare @a varchar(100),@d varchar(100) set @a=left(@code,patindex('%[0-9]%',@code)-1) --左边字母 set @d=replace(@code,@a,'') --右边数字--进位处理 if replace(@d,'9','')='' begin set @d=replace(@d,'9','0') set @a=case right(@a,1) when 'Z' then left(@a,len(@a)-2)+char(ascii(substring(@a,len(@a)-1,1))+1)+'0' else left(@a,len(@a)-1)+char(ascii(right(@a,1))+1) end end else set @d=replicate('0',len(@d)-len(@d+1))+rtrim(@d+1)--返回结果 select @a+@d /* AAB00000 */
Custom Auto-Generated Sequences with SQL Server http://blog.csdn.net/hery2002/archive/2008/04/13/2288320.aspx
DECLARE @NO VARCHAR(8), @N1 VARCHAR(8), @N2 VARCHAR(8) SET @NO='AAZ99999' SET @N1=@NO SET @N2=@NOWHILE PATINDEX('%[0-9]%', @N1)>0 SET @N1=STUFF(@N1, PATINDEX('%[0-9]%', @N1), 1, '') WHILE PATINDEX('%[A-Z]%', @N2)>0 SET @N2=STUFF(@N2, PATINDEX('%[A-Z]%', @N2), 1, '') --SELECT @N1,@N2SET @N1=REVERSE(@N1) SET @N1=REVERSE(STUFF(@N1, PATINDEX('%[^Z]%', @N1), 1, CHAR(ASCII(SUBSTRING(@N1, PATINDEX('%[^Z]%', @N1), 1))+1)) )SELECT CASE WHEN PATINDEX('%[0-8]%', @N2)=0 THEN CASE WHEN RIGHT(@N1,1)='Z' THEN LEFT(@N1,LEN(@N1)-1)+REPLICATE('0', LEN(@N2)+1) ELSE @N1+REPLICATE('0', LEN(@N2)) END ELSE @N1+RTRIM(CAST(@N2 AS INT)+1) END /* AB000000 */
create function NextCode(@code varchar(100)) returns varchar(100) as begin --拆分 declare @a varchar(100),@d varchar(100) set @a=left(@code,patindex('%[0-9]%',@code)-1) --左边字母 set @d=replace(@code,@a,'') --右边数字--进位处理 if replace(@d,'9','')='' --需要进位 begin set @d=replace(@d,'9','0') set @a=case right(@a,1) when 'Z' then left(@a,len(@a)-2)+char(ascii(substring(@a,len(@a)-1,1))+1)+'0' else left(@a,len(@a)-1)+char(ascii(right(@a,1))+1) end end else --不需要进位 set @d=replicate('0',len(@d)-len(@d+1))+rtrim(@d+1)--返回结果 return @a+@d end go--测试: select dbo.NextCode('AAA99999') --AAB00000 select dbo.NextCode('AAZ99999') --AB000000--删除函数 drop function NextCode
Truncate 这个表,标识列有从0开始了
set @code='AAA99999'
--set @code='AAZ99999'--拆分
declare @a varchar(100),@d varchar(100)
set @a=left(@code,patindex('%[0-9]%',@code)-1) --左边字母
set @d=replace(@code,@a,'') --右边数字--进位处理
if replace(@d,'9','')=''
begin
set @d=replace(@d,'9','0')
set @a=case right(@a,1) when 'Z' then left(@a,len(@a)-2)+char(ascii(substring(@a,len(@a)-1,1))+1)+'0'
else left(@a,len(@a)-1)+char(ascii(right(@a,1))+1) end
end
else
set @d=replicate('0',len(@d)-len(@d+1))+rtrim(@d+1)--返回结果
select @a+@d
/*
AAB00000
*/
http://blog.csdn.net/hery2002/archive/2008/04/13/2288320.aspx
SET @NO='AAZ99999'
SET @N1=@NO
SET @N2=@NOWHILE PATINDEX('%[0-9]%', @N1)>0
SET @N1=STUFF(@N1, PATINDEX('%[0-9]%', @N1), 1, '')
WHILE PATINDEX('%[A-Z]%', @N2)>0
SET @N2=STUFF(@N2, PATINDEX('%[A-Z]%', @N2), 1, '')
--SELECT @N1,@N2SET @N1=REVERSE(@N1)
SET @N1=REVERSE(STUFF(@N1,
PATINDEX('%[^Z]%', @N1),
1,
CHAR(ASCII(SUBSTRING(@N1, PATINDEX('%[^Z]%', @N1), 1))+1))
)SELECT CASE WHEN PATINDEX('%[0-8]%', @N2)=0
THEN CASE WHEN RIGHT(@N1,1)='Z' THEN LEFT(@N1,LEN(@N1)-1)+REPLICATE('0', LEN(@N2)+1) ELSE @N1+REPLICATE('0', LEN(@N2)) END
ELSE @N1+RTRIM(CAST(@N2 AS INT)+1)
END
/*
AB000000
*/
returns varchar(100)
as
begin
--拆分
declare @a varchar(100),@d varchar(100)
set @a=left(@code,patindex('%[0-9]%',@code)-1) --左边字母
set @d=replace(@code,@a,'') --右边数字--进位处理
if replace(@d,'9','')='' --需要进位
begin
set @d=replace(@d,'9','0')
set @a=case right(@a,1) when 'Z' then left(@a,len(@a)-2)+char(ascii(substring(@a,len(@a)-1,1))+1)+'0'
else left(@a,len(@a)-1)+char(ascii(right(@a,1))+1) end
end
else --不需要进位
set @d=replicate('0',len(@d)-len(@d+1))+rtrim(@d+1)--返回结果
return @a+@d
end
go--测试:
select dbo.NextCode('AAA99999') --AAB00000
select dbo.NextCode('AAZ99999') --AB000000--删除函数
drop function NextCode