sqlshots: Add Leading Zeros

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')

Posted via email from wetmatter nonsense