Pad a string with leading zeros so it's 3 characters long in SQL Server 2008

Question

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?

Possible duplicate of [Most efficient T-SQL way to pad a varchar on the left to a certain length?](https://stackoverflow.com/questions/121864/most-efficient-t-sql-way-to-pad-a-varchar-on-the-left-to-a-certain-length) — underscore_d, Mar 23 '18 at 14:25
Possible duplicate of [Formatting Numbers by padding with leading zeros in SQL Server](https://stackoverflow.com/questions/9520661/formatting-numbers-by-padding-with-leading-zeros-in-sql-server) — , Mar 23 '18 at 18:16

Hogan · Accepted Answer · 2018-10-09T18:00:05.020

892

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

edited Oct 09 '18 at 18:00

answered May 26 '13 at 15:54

Hogan

69,564
10
76
117

10

I had a Char(6) field that had a handful of values that were only 2-3 chars long and the above didn't work for me. I had to add an RTRIM around the '000000'+ISNULL(FIELD,'') for it to work. – DWiener Apr 15 '14 at 02:25
@DWiener - Yes if you want a longer string then you need that many `0` characters before you do the trim – Hogan Apr 15 '14 at 04:09
3

Hogan yeah I got that, but no matter how long the string it didn't work, I'm a little too busy to figure out why but the gist of it is that with my CHAR(6) field just doing RIGHT('000000'+ISNULL(field,''),6) didn't work but RIGHT(RTRIM('000000'+ISNULL(field,'')),6) did. – DWiener Apr 16 '14 at 02:38
2

oh I understand, you had spaces to the right of a number encoded as a string. – Hogan Apr 16 '14 at 09:51
3

@dwiener you got this behaviour because a char is a fixed length data type, so in your case char(6) means 6 chars long. If your actual value is less than 6 it is padded with blanks to the right so the proposed answer would produce incorect result for a char(6). – Giannis Paraskevopoulos Sep 30 '14 at 18:29
@GiannisParaskevopoulos - or the number is saved in the DB padded on the right with characters – Hogan Sep 30 '14 at 18:37
1

please add information about [`lpad`](https://www.w3schools.com/sql/func_mysql_lpad.asp) in yout answer – diralik Dec 23 '17 at 11:07
1

@diraria - lpad is a function in MySQL, this is a question about SQLServer – Hogan Dec 23 '17 at 12:19
2

@Hogan, yes, but this question is top1 google result for "sql add leading zeros", so i think it would be usefull for many people (who don't use sqlserver, but google this question) to know that in other databases may exists more convient function lpad. Thank you anyway. – diralik Dec 23 '17 at 12:21
Hogan, @diraria does have a point but diraria, I believe that you should add the answer. Hogan's answer is a perfect. Don't touch it. :) Funny thing is, Hogan edited the tag in the question to include sql which is why it's #1 for "sql leading zeros" search but that's not the case if you search "mysql leading zeros"... – Larry Beasley Feb 21 '18 at 15:56
@LarryB -- mySQL is the one of the least standard of bigger platforms. But this answer is not as generic as it could be -- ISNULL should be COALESCE for example. – Hogan Feb 21 '18 at 20:19
1

Did not work as expected, when the length if greater than 3 – Arun Prasad E S Sep 13 '18 at 14:06
1

@ArunPrasadES -- It states in the question that the string needs to be 3 characters. My answer would be wrong if it created a string greater than 3. – Hogan Oct 09 '18 at 17:58

Géza · Answer 2 · 2018-06-22T13:58:52.897

213

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

edited Jun 22 '18 at 13:58

answered May 04 '16 at 12:35

Géza

2,492
1
17
12

2

What happens if the input number is 111 or 11? – Hogan Aug 15 '16 at 15:15
6

For 1 it's 001, for 11 it's 011 and for 111 it's 111 – Géza Aug 22 '16 at 12:52
1

it seems to be considerably slower than the accepted answer but sooo much easier if not working with large amounts of data – root Sep 26 '18 at 13:06
4

Although it seems illogical, it's worth noting that FORMAT only works with numeric and date types, not varchar. – strattonn Nov 30 '18 at 03:00
You also cannot use Expression like SUBSTRING as an expression. – Unbound Jan 17 '19 at 13:50

score 180 · Answer 3 · answered Apr 17 '15 at 21:37

180

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.

answered Apr 17 '15 at 21:37

Anon

10,660
1
29
31

1

Becareful with this method. When the expression exceeds the specified length, the string returns ** for the specified length. for e.g. str(n, 10), when n = 1000000000 then you will have stars (*) appearing. – Unbound Jan 17 '19 at 13:46
3

Careful with this one, strings break it (and the OP asked for "padding a string"). Works: `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. – Jeff Mergler Apr 02 '19 at 23:56
2

@Unbound This is how it's intended to work, the poster already says it. Better to return *** than a truncated value as all the other proposals do, it shows that the parameters were wrong. – Marc Guillot Jan 08 '20 at 08:31
This effectively breaks for N between -9 and -1; It'll return something like `0-9` – Tim Lehner Jul 24 '20 at 17:34

score 49 · Answer 4 · answered Mar 27 '15 at 16:46

49

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)

answered Mar 27 '15 at 16:46

jahu

5,427
3
37
64

2

As far as I remember CONCAT just ignores the value if it is null so the first one works fine. – Marie Oct 24 '18 at 17:12
This solution would work regardless of the len of Field – Unbound Jan 18 '19 at 09:13
Be careful, this solution will break if the length of the field is greater than 3. SELECT RIGHT(CONCAT('000', '87679'), 3) --> 679 – Harshit Mahajan Dec 23 '21 at 03:25
you can use like this to solve the issue if length is greater than three: SELECT RIGHT(CONCAT('000', field), CASE WHEN LEN(field) <3 THEN 3 ELSE LEN(field) END) – Harshit Mahajan Dec 23 '21 at 03:31

score 35 · Answer 5 · edited Jul 27 '22 at 22:42

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 = COALESCE(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.

@StenPetrov, Thank you. It all depends on what you're trying to accomplish. The one thing I've learned to depend on in large, real-world production databases is the presence of bad data of one sort or another. And I prefer to avoid the 3 AM phone calls if I possibly can ;^) — Nicholas Carey, Jul 10 '14 at 18:39
:) still when that 3AM call comes in I'd much rather have to read 1 simple line than 10 complex ones. Adding variables further makes things worse, especially if another team member decided to calculate them on the fly and didn't check for non-negative @width... — Sten Petrov, Jul 11 '14 at 05:09
Those added variables are just for generalization -- you can hard code the values. For the one liner, you can create a scalar function -- then you have your one liner. — Gerard ONeill, Apr 30 '15 at 18:45

score 27 · Answer 6 · edited Dec 10 '14 at 15:11

27

I have always found the following method to be very helpful.

REPLICATE('0', 5 - LEN(Job.Number)) + CAST(Job.Number AS varchar) as 'NumberFull'

edited Dec 10 '14 at 15:11

User999999

2,500
7
37
63

answered Dec 10 '14 at 15:08

Bill

271
3
4

score 19 · Answer 7 · answered May 08 '16 at 05:47

19

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

answered May 08 '16 at 05:47

Salar

2,088
21
26

1

This should be the accepted answer because it works on numbers *and strings*. And if you don't want to use a function (but why not) something like this also works: `DECLARE @NumStr VARCHAR(250) = '2016'; SELECT REPLICATE('0', 12 - LEN(@NumStr)) + @NumStr;` which returns Salar's first example above. Thanks Salar. – Jeff Mergler Apr 03 '19 at 00:13
My comment above contained a typo, it should read: `DECLARE @NumStr VARCHAR(250) = '2016'; SELECT REPLICATE('0', 10 - LEN(@NumStr)) + @NumStr;` which returns `0000002016` in the first example above. – Jeff Mergler Apr 03 '19 at 17:42
@JeffMergler - how does this work on numbers and strings? It is a function that takes an integer parameter. The question was about strings. – Hogan Jun 03 '20 at 14:38

score 7 · Answer 8 · answered Apr 09 '19 at 09:27

7

Try this with fixed length.

select right('000000'+'123',5)

select REPLICATE('0', 5 - LEN(123)) + '123'

answered Apr 09 '19 at 09:27

Dr.Stark

116
1
4

score 6 · Answer 9 · answered Jul 10 '14 at 16:29

6

For those wanting to update their existing data here is the query:

update SomeEventTable set eventTime=RIGHT('00000'+ISNULL(eventTime, ''),5)

answered Jul 10 '14 at 16:29

Alan B. Dee

5,490
4
34
29

score 5 · Answer 10 · edited Aug 31 '20 at 06:33

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.

Konstantin · Answer 11 · 2014-06-08T11:32:45.813

4

For integers you can use implicit conversion from int to varchar:

SELECT RIGHT(1000 + field, 3)

edited Jun 08 '14 at 11:32

answered Apr 25 '14 at 08:13

Konstantin

3,294
21
23

4

However, that will fail given a sufficiently large value, further, for negative values, you'll get...interesting results. – Nicholas Carey Jul 10 '14 at 18:07

score 2 · Answer 12 · answered Oct 21 '15 at 20:42

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.

score 2 · Answer 13 · answered Oct 24 '18 at 21:28

2

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)

answered Oct 24 '18 at 21:28

ncastillo

41
4

Discovery · Answer 14 · 2022-06-16T12:48:10.517

2

Finally I decide to use this:

RIGHT(STUFF(ReceiptNum, 1, 0, replicate('0',10)),10)

edited Jun 16 '22 at 12:48

answered Jun 16 '22 at 12:44

Discovery

41
4

score 1 · Answer 15 · edited Aug 27 '15 at 04:57

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

score 1 · Answer 16 · edited Jan 13 '18 at 03:45

1

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

edited Jan 13 '18 at 03:45

Tom Aranda

5,919
11
35
51

answered Jan 12 '18 at 13:14

DEBASIS PAUL

11
1

score 0 · Answer 17 · answered Mar 27 '20 at 16:45

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'

score -1 · Answer 18 · edited Oct 21 '19 at 07:35

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

Pad a string with leading zeros so it's 3 characters long in SQL Server 2008

18 Answers18

Linked

Related