You can also use FORMAT() function introduced in SQL Server 2012. http://technet.microsoft.com/library/hh213505.aspx

DECLARE @number1 INT, @number2 INT

SET @number1 = 1
SET @number2 = 867

SELECT FORMAT(@number1, 'd10')
SELECT FORMAT(@number2, 'd10')

If you are looking for converting values in the column for your purpose to use in application, you can use this following-

SELECT CAST(your_column AS VARCHAR(100))
--VARCHAR length based on your data

But if you are looking for change data type of your database column directly, you can try this-

ALTER TABLE TableName 
ALTER COLUMN your_column VARCHAR(200) NULL
--NULL or NOT NULL based on the data already stored in database

You must read about data types. An INT is noting more than a bit pattern. Whenever you see the number in a human readable format, the actual value is translated to a string consisting of digits. But this digit format is not the actual INT.

Leading Zeros are never part of the INT itself, but may be added to the string representation. So your question (taken literally) does not make any sense actually.

If there is a string like 00012 and you want to use it like a number, you should just cast it:

SELECT CAST('00012' AS INT) + 2; --14

Other answers show you some approaches to get a padded string representation out of an INT, but this is the opposite direction:

SELECT REPLACE((STR(12,5),' ','0'); --00012

You can combine these approaches:

DECLARE @PaddedNumber CHAR(5)='00012'
SELECT REPLACE(STR(CAST(@PaddedNumber AS INT) + 2,5),' ','0'); --00014

The padded number (which is - by type! - a string) is casted to an INT, then used in computation. The result is an INT, which can be converted to a padded string. But the final result's type is string...

This is the most elegant solution I can think of:

Select isnull(replicate('0', 7 - len(column)),'') + rtrim(column) from table

Examples:

Select isnull(replicate('0', 7 - len('123')),'') + rtrim('123')
Output: 0000123

Select isnull(replicate('0', 7 - len('1234567')),'') + rtrim('1234567')
Output: 1234567

Select isnull(replicate('0', 7 - len('12345678')),'') + rtrim('12345678')
Output: 12345678

Explanation:

• The cast isn't required because your column is already a varchar
• the rtrim might as well be around the shortest value possible.
• replicate() returns NULL if the value is negative, so we replace this with an empty string.

as of SQL Server 2012 you can now do this:

format(@int, '0000#')