Adding Text Padding with an SQL Function

This simple little function is used all over our various databases, and gives us a nice way of guaranteeing the format of a string.

What it does

It takes three input values:

@PadChar is the character to be repeated at the start.
@PadValue is the value to be padded
@PadLen is the length of the new string.

So we could pad a value of "1" to "00001" if a system required it by using the following command.

SELECT dbo.TextPad('0','1',5)

SQL

CREATE FUNCTION [dbo].[TextPad](@PadChar CHAR(1),@PadValue NVARCHAR(100),@PadLen INT) RETURNS NVARCHAR(100) AS BEGINRETURN ISNULL(REPLICATE(@PadChar,@PadLen-LEN(@PadValue))+@PadValue,LEFT(@PadValue,@PadLen))END