[MSSQL]向左填充指定字符串

 

go

--创建函数(该函数来自csdn，作者不详)
create function [dbo].[padleft]
(
@str varchar(50), --需要填充的字符串
@totalwidth int, --填充后的长度
@paddingchar char(1)--填充使用的字符
)
returns varchar(1000) as 
begin 
declare @s varchar(100)
set @s = @str
if ( len(@str) < @totalwidth)
begin
declare @i int
declare @strlen int
declare @temp varchar(100)
set @i = 1; 
set @strlen = @totalwidth - len(@str)
set @temp = '';
while(@i <= @strlen )
begin
set @temp = @temp + @paddingchar;
set @i = @i + 1;
end
set @s = @temp + @str
end
return (@s)
end
go
--测试示例
declare @table table (id nvarchar(20))
insert into @table
select '1' union all
select '2' union all
select '3' union all
select '4' union all
select '5' union all

select '6'