create function fid(@i as int)
returns varchar(4)
as
begin
declare @j varchar(4)
set @j=cast(@i as varchar(4))
set @j=replicate('0',4-len(@j))+@j
return @j
end
go
select dbo.fid(1)
returns varchar(4)
as
begin
declare @j varchar(4)
set @j=cast(@i as varchar(4))
set @j=replicate('0',4-len(@j))+@j
return @j
end
go
select dbo.fid(1)
declare @t0 int
declare @t1 varchar(8)
declare @t2 varchar(4)
set @t0 = 1
set @t1 = '0000'+ '21'
set @t2 = substring(@t1,len(@t1) - 4+1,len(@t1))
select @t2,@t1或
--@t0为原值,@t1为中间量,@t2为目标值
declare @t0 int
declare @t1 varchar(8)
declare @t2 varchar(4)
set @t0 = 1
set @t1 = '0000'+ '21'set @t2 = right(@t1, 4)
select @t2,@t1
测试结果:
0021 000021用char(8),char(4)时会出错
--@t0为原值,@t1为中间量,@t2为目标值
declare @t0 int
declare @t1 varchar(8)
declare @t2 varchar(4)
set @t0 = 1
set @t1 = '0000'+ cast(@t0 as varchar(4))
set @t2 = right( @t1, 4 )
select @t2,@t1
测试结果:
0021 000021如果@t0是varchar类型,只要set @t2 = right('0000'+ @t0,4)就可以了(已经测试)