Typically in most cases you find yourself removing leading zeros but in this case I needed to add leading zeros to a column.
Remove Leading Zeros
SELECT CAST(CAST(ColumnName AS INT) AS VARCHAR(10)) FROM TableName
Let's Pad the field
Since I needed 6 chars this will add 3 spaces to the front of the numbers
SELECT STR(ColumnName, 6) FROM TableName
Add Leading Zeros
Let's say we need to add zeros to an employee id, well this would be an easy way to accomplish it.
SELECT REPLACE(STR(ColumnName, 6), SPACE(1), '0') FROM TableName
SELECT SalesPersonID ,REPLACE(STR(SalesPersonID, 6), SPACE(1), '0') AS PaddedSalesPID FROM Sales.SalesPerson
Note: the SPACE(1) is equivalent to ' ' (That is a Tick Space Tick) So two Ticks with a space in between)
Update Table
UPDATE Sales.SalesPerson SET newSalesPID = REPLACE(STR(SalesPersonID, 6), SPACE(1), '0')
