sql函数PadLeft与PadRight代码实例

1、PadLeft函数
向已知字符串左边补充字符，使整个字符串到达指定长度

 

CREATE FUNCTION PadLeft
(
    @VarOrgan varchar(10),/*原始字符*/
    @TotalLength int,/*总长度*/
    @Placeholder varchar(1)/*占位符*/
)
RETURNS varchar(100)
AS
BEGIN
    declare @VarNew varchar(100),
            @PadLen int
    SET @VarNew=@VarOrgan
    SET @PadLen=@TotalLength-LEN(@VarOrgan)
    while(@PadLen>0)
        begin
            select @VarNew=@Placeholder+@VarNew
            set @PadLen=@PadLen-1
        end
    return @VarNew
END
 

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

 

CREATE FUNCTION PadRight
(
    @VarOrgan varchar(10),/*原始字符*/
    @TotalLength int,/*总长度*/
    @Placeholder varchar(1)/*占位符*/
)
RETURNS varchar(100)
AS
BEGIN
    declare @VarNew varchar(100),
            @PadLen int
    SET @VarNew=@VarOrgan
    SET @PadLen=@TotalLength-LEN(@VarOrgan)
    while(@PadLen>0)
        begin
            select @VarNew=@VarNew+@Placeholder
            set @PadLen=@PadLen-1
        end
    return @VarNew
END
 

 

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