In .NET there are nice functions for padding like PadLeft and PadRight. Found out SQL has its own: the replicate command. You pass a string (the value you want to pad) and then an integer value for how many occurences you want. Combine that command with the RIGHT or LEFT commands to pad any value. For example:
RIGHT(
REPLICATE('0', 4) + CAST(@rmaNo AS VARCHAR(4)),4)
In this example I have a variable "@rmaNo" that I want to left pad with zeros to make it 4 characters. To accomplish this I convert the variable to a varchar value and then add the padded zeros to the left. Then I take the rightmost 4 characters to get the value I want.