declare @a char(8) declare @i int set @i = 1 set @a = substring(rtrim(100000000 + @i),2,8) print @a
create table temp (id int,bh varchar(8)) insert into temp select 1,'00000001' insert into temp select 2,'00000002' alter function T() returns varchar(8) as begin declare @bh varchar(8) select top 1 @bh=bh from temp order by id desc set @bh=cast(replace(@bh,'0','') as int)+1 declare @num int declare @count int set @num=0 set @count=len(@bh) while(@num<8-@count) begin set @bh='0'+@bh set @num=@num+1 end return @bh end执行下面语句: select dbo.T()
字符串固定长度为8位. 比如数字 1 如何生成'00000001' 2 00000002 3 00000003 ..... 99999999 这种格式. ---------------------- declare @i int set @i = 1 while @i < 15 begin print replicate(0, 8 - len(@i)) + cast(@i as varchar) set @i = @i + 1 end /* 00000001 00000002 00000003 00000004 00000005 00000006 00000007 00000008 00000009 00000010 00000011 00000012 00000013 00000014 */
declare @a int set @a=111 select right('00000000'+cast(@a as varchar(8)),8)
select right('00000000' + cast(id as varchar),8) from tb
declare @i int
set @i = 1
set @a = substring(rtrim(100000000 + @i),2,8)
print @a
(id int,bh varchar(8))
insert into temp select 1,'00000001'
insert into temp select 2,'00000002'
alter function T()
returns varchar(8)
as
begin
declare @bh varchar(8)
select top 1 @bh=bh from temp order by id desc
set @bh=cast(replace(@bh,'0','') as int)+1
declare @num int
declare @count int
set @num=0
set @count=len(@bh)
while(@num<8-@count)
begin
set @bh='0'+@bh
set @num=@num+1
end
return @bh
end执行下面语句:
select dbo.T()
比如数字 1 如何生成'00000001'
2 00000002
3 00000003
.....
99999999
这种格式.
----------------------
declare @i int
set @i = 1
while @i < 15
begin
print replicate(0, 8 - len(@i)) + cast(@i as varchar)
set @i = @i + 1
end
/*
00000001
00000002
00000003
00000004
00000005
00000006
00000007
00000008
00000009
00000010
00000011
00000012
00000013
00000014
*/
set @a=111
select right('00000000'+cast(@a as varchar(8)),8)