It is often necessary to prepend zeros to a string of fixed length. The following Transact SQL function prepends zeros to a string. The number of zeros depends on the given @strLength value.

/* -----------------------------------------------------------------------------

Prepend Zeros to String Function

It is often necessary to prepend zeros to a string of fixed length. The
following function prepends zeros to a string. The number of zeros depends
on the given @strLength value.

    Example 1: @strValue = 123456, @strLength = 10
    result = 0000123456

    Example 2: @strValue = 123456789A, @strLength = 10
    result = 123456789A (not touched because fixed length is already 10)

----------------------------------------------------------------------------- */

CREATE FUNCTION FN_PREPEND_ZEROS_TO_STRING
(
    @strValue varchar(2000),
    @strLength int
)
RETURNS varchar(2000)
AS
BEGIN
    IF LEN(@strValue) <= @strLength
    BEGIN
        WHILE(LEN(@strValue) < @strLength)
        BEGIN
            SET @strValue = '0' + @strValue
        END
    END
    RETURN @strValue
END