lots of helpful stuff in here - leaving this as an artifact for others who find this article in a google search like me - I was trying to figure this out today with Zip Codes which was a mess because we wanted what should be the left 5 but some leading 0's were trimmed so some were 3 digits that needed two leading 0's padded and some were 7 digits that needed two padded, so I ended up going with a case statement - not super elegant but efficient and got the job done.

UPDATE q
SET q.Zip =
    CASE WHEN LEN(q.Zip) IN (4,8) THEN '0'+ LEFT(q.Zip,4)
         WHEN LEN(q.Zip) IN (3,7) THEN '00'+ LEFT(q.Zip,3)
         WHEN LEN(q.Zip) IN (2,6) THEN '000'+ LEFT(q.Zip,2)
         WHEN LEN(q.Zip) = 1 THEN '0000'+ LEFT(q.Zip,1) 
        ELSE LEFT(q.Zip,5)
    END
FROM #BadData q