declare @t int,@sql nvarchar
select @t=max(cast(substring(编号,4,len(编号)-6) as int))+1 from 表
set @sql=cast(@t as nvarchar)
while len(@sql)<len(编号)-6
begin
set @sql=''
@sql='0'+@sql
end
set @sql='aaa'+@sql+'bbb'
insert into 表(编号) values(@sql)
declare @t int,@sql nvarchar
select @t=cast(substring(编号,4,len(编号)-6) as int)+1 from 表
@sql='aaa'+cast(@t as nvarhchar)+'bbb'
insert into 表(编号) values(@sql)
create function fn_NextNo
returns varchar(20)
as
begin
declare @r int
select @r=isnull(max(cast(substring(编号,4,3) as int))+1,1) from a where 编号 like 'aaa%bbb'
return 'aaa'+right('000'+cast(@r as varchar),3)+'bbb'
end
go