How to append '000000000000000' to a nvarchar(255) column RRS feed

  • Question

  • I need to format the column data to 15 char begining with 000.  I am trying to replicat the excel TEXT(2034702,"000000000000000") formula.  I have tried a few things and always get result as 2.03469e+006 instead of 


    Monday, December 31, 2018 4:13 PM

All replies

  • Just tried FORMAT(CompanyID , '000000000000000') and that worked is there a better way?


    Monday, December 31, 2018 4:16 PM
  • SELECT replace(str('2034702', 15, 0), ' ', '0')

    Erland Sommarskog, SQL Server MVP,

    Monday, December 31, 2018 4:30 PM
  • select REPLICATE('0', 15 - LEN(2034702)) + CAST(2034702 AS varchar)
    Monday, December 31, 2018 4:44 PM
  • Another one...

    SELECT Right('000000000000000'+LTrim(str('2034702')),15)

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped.
     [Blog]  [LinkedIn]

    Monday, December 31, 2018 4:50 PM
  • Hi John Basedow,

    Thank you for your posting.


    Would you like this?

    declare @number varchar(15)=2034702
    select stuff('000000000000000'+@number,1,14-len(@number),'') as LAC

    Best Regards,


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact

    Tuesday, January 1, 2019 6:55 AM