Integer PadLeft function in T-SQL

Question

I have the following table A:

id
----
1
2
12
123
1234

I need to left-pad the id values with zero's:

id
----
0001
0002
0012
0123
1234

How can I achieve this?

Answer 1

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

Answer 2

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

Answer 3

Old post, but maybe this helps someone out:

To complete until it ends up with 4 non-blank characters:

SELECT RIGHT ('0000'+COLUMNNAME, 4) FROM TABLENAME;

To complete until 10:

SELECT RIGHT ('0000000000'+COLUMNNAME, 10) FROM TABLENAME;

In case the column is numeric, convert it to varchar first with such code:

Select RIGHT('0000'+Convert(nvarchar(20), COLUMNNAME), 4)
From TABLENAME

And to complete until 10 with a numeric field:

SELECT RIGHT ('0000000000'+Convert(nvarchar(20), COLUMNNAME), 10) FROM TABLENAME;

Answer 4

declare @T table(id int)
insert into @T values
(1),
(2),
(12),
(123),
(1234)

select right('0000'+convert(varchar(4), id), 4)
from @T

Result

----
0001
0002
0012
0123
1234

Answer 5

Try this:

SELECT RIGHT(REPLICATE('0',4)+CAST(Id AS VARCHAR(4)),4) FROM [Table A]

Answer 6

-- Please look into these.

select FORMAT(1, 'd4');
select FORMAT(2, 'd4');
select FORMAT(12, 'd4');
select FORMAT(123, 'd4');
select FORMAT(1234, 'd4');

-- I hope these would help you

Answer 7

This works for strings, integers and numeric:

SELECT CONCAT(REPLICATE('0', 4 - LEN(id)), id)

Where 4 is desired length. Works for numbers with more than 4 digits, returns empty string on NULL value.

Answer 8

If someone is still interested, I found this article on DATABASE.GUIDE:
Left Padding in SQL Server – 3 LPAD() Equivalents

In short, there are 3 methods mentioned in that article.
Let's say your id=12 and you need it to display as 0012.

Method 1 – Use the RIGHT() Function
The first method uses the RIGHT() function to return only the rightmost part of the string, after adding some leading zeros.

SELECT RIGHT('00' + '12', 4);

Result:
0012

Method 2 – Use a Combination of RIGHT() and REPLICATE()
This method is almost the same as the previous method, with the only difference being that I simply replace the three zeros with the REPLICATE() function:

SELECT RIGHT(REPLICATE('0', 2) + '12', 4);

Result:
0012

Method 3 – Use a Combination of REPLACE() and STR()
This method comes from a completely different angle to the previous methods:

SELECT REPLACE(STR('12', 4),' ','0');

Result:
0012

Check out the article, there is more in depth analysis with examples.

Answer 9

This is what I normally use when I need to pad a value.

SET @PaddedValue = REPLICATE('0', @Length - LEN(@OrigValue)) + CAST(@OrigValue as VARCHAR)

Answer 10

I created a function to do this, where you can specify the desired output character length:

CREATE FUNCTION [dbo].[udfLeadingZero]
(
        @String VARCHAR(MAX)
,       @Len INT
)
RETURNS VARCHAR(MAX)
BEGIN
    SET @String = RIGHT(REPLICATE('0',@Len)+@String,@Len)
RETURN @String
END
GO

Example results

Answer 11

I needed this in a function on SQL server and adjusted Patrick's answer a bit.

declare @dossierId int = 123
declare @padded_id varchar(7)


set @padded_id = REPLACE(
              SPACE(7 - LEN(@dossierId)) + convert(varchar(7), @dossierId), 
              SPACE(1),  
              '0') 

SELECT @dossierId as '@dossierId'
      ,SPACE(LEN(@dossierId)) + convert(varchar(7)
      ,@dossierId) as withSpaces
      ,@padded_id as '@padded_id'

Answer 12

Create Function :

    Create FUNCTION [dbo].[PadLeft]
      (
        @Text NVARCHAR(MAX) ,
        @Replace NVARCHAR(MAX) ,
        @Len INT
      )
RETURNS NVARCHAR(MAX)
AS
    BEGIN 


        DECLARE @var NVARCHAR(MAX) 

        SELECT @var = ISNULL(LTRIM(RTRIM(@Text)) , '')


        RETURN   RIGHT(REPLICATE(@Replace,@Len)+ @var, @Len)


    END

Example:

Select dbo.PadLeft('123456','0',8)

Answer 13

I created a function:

CREATE FUNCTION [dbo].[fnPadLeft](@int int, @Length tinyint)
RETURNS varchar(255) 
AS 
BEGIN
    DECLARE @strInt varchar(255)

    SET @strInt = CAST(@int as varchar(255))
    RETURN (REPLICATE('0', (@Length - LEN(@strInt))) + @strInt);
END;

Use: select dbo.fnPadLeft(123, 10)

Returns: 0000000123

Answer 14

Something fairly ODBC compliant if needed might be the following:

select ifnull(repeat('0', 5 - (floor(log10(FIELD_NAME)) + 1)), '')
        + cast (FIELD as varchar(10))
  from TABLE_NAME

This bases on the fact that the amount of digits for a base-10 number can be found by the integral component of its log. From this we can subtract it from the desired padding width. Repeat will return null for values under 1 so we need ifnull.

Answer 15

My solution is not efficient but helped me in situation where the values (bank cheque numbers and wire transfer ref no.) were stored as varchar where some entries had alpha numeric values with them and I had to pad if length is smaller than 6 chars.

Thought to share if someone comes across same situation

declare @minlen int = 6
declare @str varchar(20)

set @str = '123'
select case when len(@str) < @minlen then REPLICATE('0',@minlen-len(@str))+@str else @str end
--Ans: 000123

set @str = '1234'
select case when len(@str) < @minlen then REPLICATE('0',@minlen-len(@str))+@str else @str end
--Ans: 001234

set @str = '123456'
select case when len(@str) < @minlen then REPLICATE('0',@minlen-len(@str))+@str else @str end
--Ans: 123456

set @str = '123456789'
select case when len(@str) < @minlen then REPLICATE('0',@minlen-len(@str))+@str else @str end
--Ans: 123456789

set @str = '123456789'
select case when len(@str) < @minlen then REPLICATE('0',@minlen-len(@str))+@str else @str end
--Ans: 123456789


set @str = 'NEFT 123456789'
select case when len(@str) < @minlen then REPLICATE('0',@minlen-len(@str))+@str else @str end
--Ans: NEFT 123456789

Answer 16

A simple example would be

    DECLARE @number INTEGER
    DECLARE @length INTEGER
    DECLARE @char   NVARCHAR(10)
    SET @number = 1
    SET @length = 5
    SET @char = '0'

    SELECT FORMAT(@number, replicate(@char, @length))

Answer 17

More efficient way is :

Select id, LEN(id)
From TableA
Order by 2,1 

The result :
id
----
1
2
12
123
1234