Pad a string with leading zeros so it's 3 characters long in SQL Server 2008
I have a string that is up to 3 characters long when it's first created in SQL Server 2008 R2.
I would like to pad it with leading zeros, so if its original value was '1' then the new value would be '001'. Or if its original value was '23' the new value is '023'. Or if its original value is '124' then new value is the same as original value.
I am using SQL Server 2008 R2. How would I do this using T-SQL?
If the field is already a string, this will work
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
Read more... Read less...
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.
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#');
The safe method:
SELECT REPLACE(STR(n,3),' ','0')
This has the advantage of returning the string
'***' for n < 0 or n > 999, which is a nice and obvious indicator of out-of-bounds input. The other methods listed here will fail silently by truncating the input to a 3-character substring.
Here's a more general technique for left-padding to any desired width:
declare @x int = 123 -- value to be padded declare @width int = 25 -- desired width declare @pad char(1) = '0' -- pad character select right_justified = replicate( @pad , @width-len(convert(varchar(100),@x)) ) + convert(varchar(100),@x)
However, if you're dealing with negative values, and padding with leading zeroes, neither this, nor other suggested technique will work. You'll get something that looks like this:
[Probably not what you wanted]
So … you'll have to jump through some additional hoops Here's one approach that will properly format negative numbers:
declare @x float = -1.234 declare @width int = 20 declare @pad char(1) = '0' select right_justified = stuff( convert(varchar(99),@x) , -- source string (converted from numeric value) case when @x < 0 then 2 else 1 end , -- insert position 0 , -- count of characters to remove from source string replicate(@pad,@width-len(convert(varchar(99),@x)) ) -- text to be inserted )
One should note that the
convert() calls should specify an
[n]varchar of sufficient length to hold the converted result with truncation.
Here is a variant of Hogan's answer which I use in SQL Server Express 2012:
SELECT RIGHT(CONCAT('000', field), 3)
Instead of worrying if the field is a string or not, I just
CONCAT it, since it'll output a string anyway. Additionally if the field can be a
ISNULL might be required to avoid function getting
SELECT RIGHT(CONCAT('000', ISNULL(field,'')), 3)
I have always found the following method to be very helpful.
REPLICATE('0', 5 - LEN(Job.Number)) + CAST(Job.Number AS varchar) as 'NumberFull'