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](
DECLARE @Result VARCHAR(1000)
DECLARE @PadLength INT;
SET @PadLength = @StringLength –LEN(@String)
IF @PadLength <= 0
SET @Result =LEFT(@string,@StringLength)
SET @Result =REPLICATE(@PadChar,@PadLength)+ @String