none
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 
    000000002034702

    McC

    Monday, December 31, 2018 4:13 PM

All replies

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

    McC

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


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

    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
    /*
    LAC
    ---------------------
    000000002034702
    */


    Best Regards,

    Rachel


    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 MSDNFSF@microsoft.com.

    Tuesday, January 1, 2019 6:55 AM