locked
Adding leading zeroes (PADDING in SQL Server) RRS feed

  • Question

  • Hi to everyone,

    Is there a function to add leading zeros to int converted to varchar?

    I have 5, need to get varchar(5) = 00005..

    I hate to do like left('00000',5-len(Int))+cast(Int as varchar)

    I know in Oracle there is PAD, what about SQL Server?

    Thanks


    • Edited by RobbKirk Sunday, September 27, 2009 10:51 AM
    Sunday, September 27, 2009 10:45 AM

Answers

  • No, there is no PAD function, but you could simulate it.

    declare @i int, @len smallint;

    select @i = 10, @len 5;

    select right(replicate('0', @len) + ltrim(@i), @len) as padded_i;
    go

    You can create a inline tvf and use it together with the "apply" operator.


    AMB
    • Marked as answer by RobbKirk Sunday, September 27, 2009 3:48 PM
    Sunday, September 27, 2009 2:47 PM
  • Yes, I see.. so, there is nothing like PAD in SQL Server?

    no.

    check this link by Kalman

    http://www.sqlusa.com/bestpractices2005/padleadingzeros/
    • Marked as answer by Kalman Toth Wednesday, July 30, 2014 4:02 PM
    Sunday, September 27, 2009 11:34 AM

All replies

  • you can do something like this

    declare @a int
    set @a =5
    
    select right('00000' +cast(@a as nvarchar(10))  , 5)
    • Proposed as answer by Arif Hasan Sunday, September 27, 2009 11:35 AM
    Sunday, September 27, 2009 10:50 AM
  • Yes, I see.. so, there is nothing like PAD in SQL Server?
    Sunday, September 27, 2009 10:51 AM
  • Yes, I see.. so, there is nothing like PAD in SQL Server?

    no.

    check this link by Kalman

    http://www.sqlusa.com/bestpractices2005/padleadingzeros/
    • Marked as answer by Kalman Toth Wednesday, July 30, 2014 4:02 PM
    Sunday, September 27, 2009 11:34 AM
  • No, there is no PAD function, but you could simulate it.

    declare @i int, @len smallint;

    select @i = 10, @len 5;

    select right(replicate('0', @len) + ltrim(@i), @len) as padded_i;
    go

    You can create a inline tvf and use it together with the "apply" operator.


    AMB
    • Marked as answer by RobbKirk Sunday, September 27, 2009 3:48 PM
    Sunday, September 27, 2009 2:47 PM
  • There is nothing like PAD in SQLServer 2008, but SQLServer 2012 has  FORMAT ([fieldname], '00000').

    For SQLServer 2008, I've found that something like this works...

     Right('00000'+ convert(varchar,[fieldname]),5)

    Wednesday, July 30, 2014 2:24 PM