If the field is already a string, this will work
SELECT RIGHT('000'+ISNULL(field,''),3)
If you want nulls to show as '000'
It might be an integer -- then you would want
SELECT RIGHT('000'+CAST(field AS VARCHAR(3)),3)
Answer from Hogan on Stack OverflowAs required by the question this answer only works if the length <= 3, if you want something larger you need to change the string constant and the two integer constants to the width needed. eg
'0000' and VARCHAR(4)),4
If the field is already a string, this will work
SELECT RIGHT('000'+ISNULL(field,''),3)
If you want nulls to show as '000'
It might be an integer -- then you would want
SELECT RIGHT('000'+CAST(field AS VARCHAR(3)),3)
As required by the question this answer only works if the length <= 3, if you want something larger you need to change the string constant and the two integer constants to the width needed. eg
'0000' and VARCHAR(4)),4
Although the question was for SQL Server 2008 R2, in case someone is reading this with version 2012 and above, since then it became much easier by the use of FORMAT.
You can either pass a standard numeric format string or a custom numeric format string as the format argument (thank Vadim Ovchinnikov for this hint).
For this question for example a code like
DECLARE @myInt INT = 1;
-- One way using a standard numeric format string
PRINT FORMAT(@myInt,'D3');
-- Other way using a custom numeric format string
PRINT FORMAT(@myInt,'00#');
outputs
001
001
string - Integer PadLeft function in T-SQL - Stack Overflow
how to add leading zeroes before and after decimal in sql
sql server - Most efficient T-SQL way to pad a varchar on the left to a certain length? - Stack Overflow
Left Padding in TSQL
I believe this may be what your looking for:
SELECT padded_id = REPLACE(STR(id, 4), SPACE(1), '0')
FROM tableA
or
SELECT REPLACE(STR(id, 4), SPACE(1), '0') AS [padded_id]
FROM tableA
I haven't tested the syntax on the 2nd example. I'm not sure if that works 100% - it may require some tweaking - but it conveys the general idea of how to obtain your desired output.
EDIT
To address concerns listed in the comments...
@pkr298 - Yes STR does only work on numbers... The OP's field is an ID... hence number only.
@Desolator - Of course that won't work... the First parameter is 6 characters long. You can do something like:
SELECT REPLACE(STR(id,
(SELECT LEN(MAX(id)) + 4 FROM tableA)), SPACE(1), '0') AS [padded_id] FROM tableA
this should theoretically move the goal posts... as the number gets bigger it should ALWAYS work.... regardless if its 1 or 123456789...
So if your max value is 123456... you would see 0000123456 and if your min value is 1 you would see 0000000001
SQL Server now supports the FORMAT function starting from version 2012, so:
SELECT FORMAT(id, '0000') FROM TableA
will do the trick.
If your id or column is in a varchar and represents a number you convert first:
SELECT FORMAT(CONVERT(INT,id), '0000') FROM TableA
This is simply an inefficient use of SQL, no matter how you do it.
perhaps something like
right('XXXXXXXXXXXX'+ rtrim(@str), @n)
where X is your padding character and @n is the number of characters in the resulting string (assuming you need the padding because you are dealing with a fixed length).
But as I said you should really avoid doing this in your database.
I know this was originally asked back in 2008, but there are some new functions that were introduced with SQL Server 2012. The FORMAT function simplifies padding left with zeros nicely. It will also perform the conversion for you:
declare @n as int = 2
select FORMAT(@n, 'd10') as padWithZeros
Update:
I wanted to test the actual efficiency of the FORMAT function myself. I was quite surprised to find the efficiency was not very good compared to the original answer from AlexCuse. Although I find the FORMAT function cleaner, it is not very efficient in terms of execution time. The Tally table I used has 64,000 records. Kudos to Martin Smith for pointing out execution time efficiency.
SET STATISTICS TIME ON
select FORMAT(N, 'd10') as padWithZeros from Tally
SET STATISTICS TIME OFF
SQL Server Execution Times: CPU time = 2157 ms, elapsed time = 2696 ms.
SET STATISTICS TIME ON
select right('0000000000'+ rtrim(cast(N as varchar(5))), 10) from Tally
SET STATISTICS TIME OFF
SQL Server Execution Times:
CPU time = 31 ms, elapsed time = 235 ms.
You can just modify what @kareembadr4040 posted to add another RIGHT(). This should give you a number starting with 1 with 0’s between the 1 and the employee number:
SELECT RIGHT('1' + (RIGHT('000000'+CAST(employee AS VARCHAR(6)),5)), 6) as employee_number from
ls_apps.EMPLOYEE
@tnoe
I have a set of employee numbers ranging from 3-5 characters and need to pad them starting with a 1 and fill in the remaining space with zeros so they are 6 characters long. Ive used
SELECT LEFT(‘100’+ CONVERT(VARCHAR,EMPLOYEE),6) AS NUM FROM ls_apps.EMPLOYEE
which works fine until the numbers have 4 or more characters already.