For example, the empNum in an Employee table is 123 and I want to do the left padding with zeros thereby totalling it to be with 7 digits. It should look like 0000123.
Select REPLICATE('0',7 - LEN(empNum) + CAST (empNum AS varchar) as EmpNum from Employee