Left- and RightPadding in SQL Server

Although SQL Server contains a few string manipulation functions, there exists no function that allows you to left- or rightpad a string expression.
This is a bit of a pity, since left- and rightpadding is sometimes necessary.

But, no one stops you from creating your own LPAD and RPAD function offcourse; in fact, it is rather very simple. :)
Although SQL Server doesn't provide padding methods out of the box, it doesn't require a lot of work to pad a string.

For instance: suppose you have a variable @number, which contains a number. If you need to leftpad this string with zeroes so that it always contains 8 characters, you simply have to do this:

SELECT RIGHT ('00000000' + @number, 8)

If @number contains '81337', this statement will return '00081337'.

With this knowledge, it is pretty simple to create your own LPAD method. This is the code:

CREATE FUNCTION [dbo].[LPAD] (@string VARCHAR(8000), 
                              @length INT, 
                              @paddingChar CHAR(1))
AS
BEGIN
    RETURN RIGHT (REPLICATE (@paddingChar, @length) + @string, @length)
END

Creating an RPAD function is very similar:

CREATE FUNCTION [dbo].[RPAD] (@string VARCHAR(8000),
                              @length INT,
                              @paddingChar CHAR(1))
AS
BEGIN
    RETURN LEFT (@string + REPLICATE (@paddingChar, @length), @length)
END

As you might have noticed, REPLICATE is a SQL Server function which repeats a specified characters a number of times. :)