none
zero padding for strings RRS feed

  • Question

  • how is it possible to pleft pad with zeroes a string casted  from a number ?? 

    aND WHILE WE ARE AT IT WHERE HAVE ALL MY ACTIVITIES ON msdn  IN THE 15 PAST YEARS  LANDED ?? 


    claudio cannella

    Thursday, December 12, 2019 3:01 PM

All replies

  • You can use format function or cast int to string and concatenate with leading 0s.
    Thursday, December 12, 2019 3:03 PM
    Moderator
  • declare @int int=5  
    --suppose you need the result with length 5
    --Result 00005
    
    Select format(@int,'00000')
    --or
    Select Right('0000'+Cast(@int as varchar(5)),5)
    --or
    Select Right(concat('0000',@int),5)

    Thursday, December 12, 2019 3:07 PM
    Moderator
  • One more way:

    SELECT replace(str(@num, 5), ' ', '0')

    Str formats a number right-adjusted with the given width (default is 10).


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Thursday, December 12, 2019 10:42 PM
  • Hi,

    This will create a zero padded 5 digit length of value.

    create table #t1(c1 varchar(20))
    insert into #t1 values('1'),('123')

    select replicate('0',5-len(c1))+c1
    from #t1

    Mark as answer if it works. Thanks.

    Friday, December 13, 2019 1:07 AM