孤星无泪

 

PadLeft 和 PadRight

1 PadLeft

   即：向已知字符串左边补充字符，使整个字符串到达指定长度

 1 CREATE FUNCTION PadLeft
 2 (
 3     @VarOrgan varchar(10),/*原始字符*/
 4     @TotalLength int,/*总长度*/
 5     @Placeholder varchar(1)/*占位符*/
 6 )
 7 RETURNS varchar(100)
 8 AS
 9 BEGIN
10     declare @VarNew varchar(100),
11             @PadLen int
12     SET @VarNew=@VarOrgan
13     SET @PadLen=@TotalLength-LEN(@VarOrgan)
14     while(@PadLen>0)
15         begin
16             select @VarNew=@Placeholder+@VarNew
17             set @PadLen=@PadLen-1
18         end
19     return @VarNew
20 END

 

2 PadRight

   即：向已知字符串右边补充字符，使整个字符串到达指定长度

 1 CREATE FUNCTION PadRight
 2 (
 3     @VarOrgan varchar(10),/*原始字符*/
 4     @TotalLength int,/*总长度*/
 5     @Placeholder varchar(1)/*占位符*/
 6 )
 7 RETURNS varchar(100)
 8 AS
 9 BEGIN
10     declare @VarNew varchar(100),
11             @PadLen int
12     SET @VarNew=@VarOrgan
13     SET @PadLen=@TotalLength-LEN(@VarOrgan)
14     while(@PadLen>0)
15         begin
16             select @VarNew=@VarNew+@Placeholder
17             set @PadLen=@PadLen-1
18         end
19     return @VarNew
20 END

 

3 测试结果

SELECT dbo.PadLeft('7',6,'0') PadLeft,
        dbo.PadRight('6',4,'0') PadRight

 

posted on 2014-11-30 20:19  孤星无泪  阅读(707)  评论(4)  编辑  收藏  举报

导航