Tuesday, 29 September 2015

Left or Right Padding a column in SQL Server

I had a task to retrieve a number column and do left padding with zeros. The total number of digit is 7. I used the REPLICATE method to achieve this.

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