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
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
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.
SELECT REPLACE(STR('1',3),' ','0')
Breaks: SELECT REPLACE(STR('1A',3),' ','0')
. This just burned me today when a user entered a letter in the input string and I failed to test that case.
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 NULL
, using ISNULL
might be required to avoid function getting NULL
results.
SELECT RIGHT(CONCAT('000', ISNULL(field,'')), 3)
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:
00-123
[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.
I have always found the following method to be very helpful.
REPLICATE('0', 5 - LEN(Job.Number)) + CAST(Job.Number AS varchar) as 'NumberFull'
Use this function which suits every situation.
CREATE FUNCTION dbo.fnNumPadLeft (@input INT, @pad tinyint)
RETURNS VARCHAR(250)
AS BEGIN
DECLARE @NumStr VARCHAR(250)
SET @NumStr = LTRIM(@input)
IF(@pad > LEN(@NumStr))
SET @NumStr = REPLICATE('0', @Pad - LEN(@NumStr)) + @NumStr;
RETURN @NumStr;
END
Sample output
SELECT [dbo].[fnNumPadLeft] (2016,10) -- returns 0000002016
SELECT [dbo].[fnNumPadLeft] (2016,5) -- returns 02016
SELECT [dbo].[fnNumPadLeft] (2016,2) -- returns 2016
SELECT [dbo].[fnNumPadLeft] (2016,0) -- returns 2016
DECLARE @NumStr VARCHAR(250) = '2016'; SELECT REPLICATE('0', 12 - LEN(@NumStr)) + @NumStr;
which returns Salar's first example above. Thanks Salar.
DECLARE @NumStr VARCHAR(250) = '2016'; SELECT REPLICATE('0', 10 - LEN(@NumStr)) + @NumStr;
which returns 0000002016
in the first example above.
For those wanting to update their existing data here is the query:
update SomeEventTable set eventTime=RIGHT('00000'+ISNULL(eventTime, ''),5)
I know this is an old ticket but I just thought I'd share this:
I found this code which provides a solution. Not sure if it works on all versions of MSSQL; I have MSSQL 2016.
declare @value as nvarchar(50) = 23
select REPLACE(STR(CAST(@value AS INT) + 1,4), SPACE(1), '0') as Leadingzero
This returns "0023".
The 4 in the STR function is the total length, including the value. For example, 4, 23 and 123 will all have 4 in STR and the correct amount of zeros will be added. You can increase or decrease it. No need to get the length on the 23.
Edit: I see it's the same as the post by @Anon.
For integers you can use implicit conversion from int to varchar:
SELECT RIGHT(1000 + field, 3)
Try this with fixed length.
select right('000000'+'123',5)
select REPLICATE('0', 5 - LEN(123)) + '123'
I had similar problem with integer column as input when I needed fixed sized varchar (or string) output. For instance, 1 to '01', 12 to '12'. This code works:
SELECT RIGHT(CONCAT('00',field::text),2)
If the input is also a column of varchar, you can avoid the casting part.
For a more dynamic approach try this.
declare @val varchar(5)
declare @maxSpaces int
set @maxSpaces = 3
set @val = '3'
select concat(REPLICATE('0',@maxSpaces-len(@val)),@val)
Wrote this because I had requirements for up to a specific length (9). Pads the left with the @pattern ONLY when the input needs padding. Should always return length defined in @pattern.
declare @charInput as char(50) = 'input'
--always handle NULL :)
set @charInput = isnull(@charInput,'')
declare @actualLength as int = len(@charInput)
declare @pattern as char(50) = '123456789'
declare @prefLength as int = len(@pattern)
if @prefLength > @actualLength
select Left(Left(@pattern, @prefLength-@actualLength) + @charInput, @prefLength)
else
select @charInput
Returns 1234input
Simple is that
Like:
DECLARE @DUENO BIGINT
SET @DUENO=5
SELECT 'ND'+STUFF('000000',6-LEN(RTRIM(@DueNo))+1,LEN(RTRIM(@DueNo)),RTRIM(@DueNo)) DUENO
Finally I decide to use this:
RIGHT(STUFF(ReceiptNum, 1, 0, replicate('0',10)),10)
I came here specifically to work out how I could convert my timezoneoffset to a timezone string for converting dates to DATETIMEOFFSET in SQL Server 2008. Gross, but necessary.
So I need 1 method that will cope with negative and positive numbers, formatting them to two characters with a leading zero if needed. Anons answer got me close, but negative timezone values would come out as 0-5
rather than the required -05
So with a bit of a tweak on his answer, this works for all timezone hour conversions
DECLARE @n INT = 13 -- Works with -13, -5, 0, 5, etc
SELECT CASE
WHEN @n < 0 THEN '-' + REPLACE(STR(@n * -1 ,2),' ','0')
ELSE '+' + REPLACE(STR(@n,2),' ','0') END + ':00'
I created this function which caters for bigint and one leading zero or other single character (max 20 chars returned) and allows for length of results less than length of input number:
create FUNCTION fnPadNum (
@Num BIGINT --Number to be padded, @sLen BIGINT --Total length of results , @PadChar varchar(1))
RETURNS VARCHAR(20)
AS
--Pads bigint with leading 0's
--Sample: "select dbo.fnPadNum(201,5,'0')" returns "00201"
--Sample: "select dbo.fnPadNum(201,5,'*')" returns "**201"
--Sample: "select dbo.fnPadNum(201,5,' ')" returns " 201"
BEGIN
DECLARE @Results VARCHAR(20)
SELECT @Results = CASE
WHEN @sLen >= len(ISNULL(@Num, 0))
THEN replicate(@PadChar, @sLen - len(@Num)) + CAST(ISNULL(@Num, 0) AS VARCHAR)
ELSE CAST(ISNULL(@Num, 0) AS VARCHAR)
END
RETURN @Results
END
GO
--Usage:
SELECT dbo.fnPadNum(201, 5,'0')
SELECT dbo.fnPadNum(201, 5,'*')
SELECT dbo.fnPadNum(201, 5,' ')
Success story sharing