SQL Server StringPadding (String function)

Introduction

SQL Server 2008/2012 provides many string handling functions. You can find sub string, character or pattern index, left or right functions. Still you need to put some workaround if you need to pad a replicated character to the left or right side of a string. .NET and many other languages have built-in string padding functions.

Using the code

I created these function for String padding. 

• StringPadLeft - Pads a string with leading characters to a specified total length.
• StringPadRight - Pads a string with trailing characters to a specified total length.

Usage

Declare @CUSTOMER_ID as varchar(10)
Set @CUSTOMER_ID = dbo.StringPadLeft('46',10,'0')
Print @CUSTOMER_ID

StringPadLeft function

CREATE FUNCTION StringPadLeft 
 ( @String as Varchar(max),
   @Length as int,
   @Char as char(1)
 )
RETURNS Varchar(max)
AS
BEGIN

    Declare @PaddedString as Varchar(max)

    -- Return same string if @Length is smaller or equal to the length of @String value
    If(Len(@String) >= @Length )
            RETURN @String

    -- Create PaddedString
    Set @PaddedString = Replicate(@Char, @Length- Len(@String)) + @String

    -- Return the PaddedString
    RETURN @PaddedString

END

StringPadRight function

CREATE FUNCTION StringPadRight
 ( @String as Varchar(max),
   @Length as int,
   @Char as char(1)
 )
RETURNS Varchar(max)
AS
BEGIN

  Declare @PaddedString as Varchar(max)

    -- Return same string if @Length is smaller or equal to the length of @String value
    If(Len(@String) >= @Length )
            RETURN @String

    -- Create PaddedString
    Set @PaddedString = @String + Replicate(@Char, @Length- Len(@String))

    -- Return the PaddedString
    RETURN @PaddedString

END

Note : These functions will return same string if @Length is smaller or equal to the @String value.