SQL Server T-SQL LPAD & RPAD Functions (String Padding Equivalent to PadLeft & PadRight)

Here is my method for achieving left and right string padding in the Microsoft SQL Server T-SQL language. Unfortunately T-SQL does not offer functions like Oracle PL/SQL's LPAD() and RPAD() and C#'s PadLeft() and PadRight() functions. However, you can achieve the same thing using the T-SQL REPLICATE and LEN functions. Suppose you have a numeric column called Quantity and you need to return it as a string left-padded with 0's to exactly ten characters. You could do it this way:

SELECT REPLICATE('0', (10 - LEN(CAST(Quantity, VARCHAR)))) + CAST(Quantity, VARCHAR) AS PaddedQuantity FROM TableX

The calls to the CAST function are based on the assumption that the value you padding is numeric. If Quantity were already an string you could do it like this:

SELECT REPLICATE('0', (10 - LEN(Quantity))) + Quantity AS PaddedQuantity FROM TableX

In certain cases you might be concerned that that value you want to pad might be wider than your maximum number of characters. In that case you could use a CASE block to check the LEN of your input value to avoid passing a negative result to the second argument of the REPLICATE function. No need to worry about passing a 0 to REPLICATE, though: it will simply return an empty string, which is what you'd want since no padding would be necessary.
Update: I decided to go ahead and turn these into user defined functions. Here is a script for fnPadRight and fnPadLeft:

IF EXISTS (
SELECT *
FROM dbo.sysobjects
WHERE id = object_id(N'[dbo].[fnPadRight]')
AND xtype IN (N'FN', N'IF', N'TF')
)
DROP FUNCTION [dbo].[fnPadRight]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE FUNCTION fnPadRight
(
@PadChar char(1),
@PadToLen int,
@BaseString varchar(100)
)
RETURNS varchar(1000)
AS
/* ****************************************************
Description:
Pads @BaseString to an exact length (@PadToLen) using the
specified character (@PadChar). Base string will not be
trimmed. Implicit type conversion should allow caller to
pass a numeric T-SQL value for @BaseString.
Unfortunately T-SQL string variables must be declared with an
explicit width, so I chose 100 for the base and 1000 for the
return. Feel free to adjust data types to suit your needs.
Keep in mind that if you don't assign an explicit width to
varchar it is the same as declaring varchar(1).
Revision History:
Date Name Description
---- ---- -----------
**************************************************** */
BEGIN
DECLARE @Padded varchar(1000)
DECLARE @BaseLen int
SET @BaseLen = LEN(@BaseString)
IF @BaseLen >= @PadToLen
BEGIN
SET @Padded = @BaseString
END
ELSE
BEGIN
SET @Padded = @BaseString + REPLICATE(@PadChar, @PadToLen - @BaseLen)
END
RETURN @Padded
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO



IF EXISTS (
SELECT *
FROM dbo.sysobjects
WHERE id = object_id(N'[dbo].[fnPadRight]')
AND xtype IN (N'FN', N'IF', N'TF')
)
DROP FUNCTION [dbo].[fnPadRight]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE FUNCTION fnPadRight
(
@PadChar char(1),
@PadToLen int,
@BaseString varchar(100)
)
RETURNS varchar(1000)
AS
/* ****************************************************
Description:
Pads @BaseString to an exact length (@PadToLen) using the
specified character (@PadChar). Base string will not be
trimmed. Implicit type conversion should allow caller to
pass a numeric T-SQL value for @BaseString.
Unfortunately T-SQL string variables must be declared with an
explicit width, so I chose 100 for the base and 1000 for the
return. Feel free to adjust data types to suit your needs.
Keep in mind that if you don't assign an explicit width to
varchar it is the same as declaring varchar(1).
Revision History:
Date Name Description
---- ---- -----------
**************************************************** */
BEGIN
DECLARE @Padded varchar(1000)
DECLARE @BaseLen int
SET @BaseLen = LEN(@BaseString)
IF @BaseLen >= @PadToLen
BEGIN
SET @Padded = @BaseString
END
ELSE
BEGIN
SET @Padded = @BaseString + REPLICATE(@PadChar, @PadToLen - @BaseLen)
END
RETURN @Padded
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO