If the field has already a string, then the below code should work:
If you want nulls to show as '000'. Then it will be considered as an integer –Therefore, you should try below code:
SELECT RIGHT('000'+CAST(field AS VARCHAR(3)),3)
The above solution will work only if the length <= 3.
But, if you want something larger then you need to change the string constant and the two integer constants to the width that you need to do this:
For example: ‘00000' and VARCHAR(5)),5