【叶子函数分享十九】向左填充指定字符串

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'

select dbo.padleft(id,2,'0') as id from @table

--运行结果

/*

id

-------

01

02

03

04

05

06

*/

go

--创建函数(第二版)(作者：maco_wang)

create function padleftV2

(

@sql varchar(200), --需填充的字符串

@char varchar(4), --填充使用的字符

@len int --填充后的长度

)

returns varchar(200)

as

begin

return (right(replicate(@char,@len)+@sql,@len))

end

go

--测试示例

declare @table table(id int)

insert into @table(id)

select 1 union all

select 3 union all

select 6

select dbo.padleftV2(cast(id as varchar),'0',10) as id from @table

--运行结果

/*

id

-------------

0000000001

0000000003

0000000006

*/