I think this comes up a lot in day to day operations. Needing to pad the left hand side of a char/varchar field. T0 address this I made a handy dandy function that works similar to the padleft string function in .Net. There is a caveat to this code, if string length also sets the max length of string being returned. Below is the code, please test it for your application before putting it into production to make sure it has the behaviour that you need.
CREATE FUNCTION [dbo].[PadLeft](
@StringLength INT,
@String VARCHAR(1000),
@PadChar CHAR(1)
)
RETURNS VARCHAR(1000)
AS
BEGIN
DECLARE @Result VARCHAR(1000)
DECLARE @PadLength INT;
SET @PadLength = @StringLength –LEN(@String)
IF @PadLength <= 0
BEGIN
SET @Result =LEFT(@string,@StringLength)
END
ELSE
BEGIN
SET @Result =REPLICATE(@PadChar,@PadLength)+ @String
END
RETURN @Result
END
Refactoring Self 