|
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 */ |
|
|
