Have you ever looked for a function in T-SQL just to find out there isn’t anything similar that would help you with meet your goals? Well, today I needed something simple that is usually available as a function or method in most programming language: the ability to pad a string with zeros on the left side up to a specific length.

As you probably already know by the introduction above, I couldn’t find anything similar in T-SQL; so, I had to build one. In fact, because of the similarities, I have actually built two: one that pads on the left and other that pads on the right. The good thing is that it works in the same way as in other programming languages; you provide the string, the character used to pad the string, the total length of the final string, and get back a string the way you want it.

Here’s the first function:

/*
PadLeft() – Returns string with character(s) provided attached to left side of string up to total length requested
Created: 2.4.2009
Author: Sam Moreira (smoreira @ itblognow com)
*/
Create Function fnPadLeft(@i_vString Varchar(50), @i_vChar Varchar(5), @i_iLength Int)
Returns Varchar(500)
As Begin
Declare @mResult Varchar(500)

– Remove Spaces From String
Set @mResult =RTrim(LTrim(@i_vString))

– Check the need to run statements
If (@i_iLength > Len(@mResult) And @i_iLength <= 500)
Begin
– Add character(s) to left side of string
Set @mResult = Replicate(@i_vChar, (@i_iLength – Len(@mResult))) + @mResult
Set @mResult = Left(@mResult, @i_iLength)
End

Return @mResult
End

And here’s the second one:

/*
PadRight() – Returns string with character(s) provided attached to right side of string up to total length requested
Created: 2.4.2009
Author: Sam Moreira (smoreira @ itblognow com)
*/
Create Function fnPadRight(@i_vString Varchar(50), @i_vChar Varchar(5), @i_iLength Int)
Returns Varchar(500)
As Begin
Declare @mResult Varchar(500)

– Remove Spaces From String
Set @mResult =RTrim(LTrim(@i_vString))

– Check the need to run statements
If (@i_iLength > Len(@mResult) And @i_iLength <= 500)
Begin
– Add character(s) to right side of string
Set @mResult = @mResult + Replicate(@i_vChar, (@i_iLength – Len(@mResult)))
Set @mResult = Right(@mResult, @i_iLength)
End

Return @mResult
End

Enjoy!