Post reply

Add leading zero to int

  • May 2, 2011 at 11:25 am

    #250142

    I have a need to add a leading zero to an int. For example, if the number is 1 then I want to add three zeros as "0001" and if the number is 20 then I want to add 2 zeros as "0020". Need to make any number upto 999 as 4 characters.

    How do I do it?

    Thanks.

  • May 2, 2011 at 11:33 am

    #1318595

    RIGHT('0000' + convert(varchar(4), MyNumberToConvert),4)

    Edit: note that it's now a string, not a number.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • May 2, 2011 at 11:34 am

    #1318597

    Are you trying to save your value into an int field? Or display the value from an int field to the user? Or are you not even using an int datatype in the db?

    The answers vary depending. Generally, an int doesn't deal with leading zeros. It lops them off. So you'll need to typecast to another datatype and maybe typecast back out of the database if saving as an int.

    Jim

    Jim Murphy
    http://www.sqlwatchmen.com
    @SQLMurph

  • May 2, 2011 at 11:58 am

    #1318631

    I am trying to store the number in a char field. Wayne has the correct answer. Thanks Wayne, that works.

  • May 2, 2011 at 1:05 pm

    #1318677

    ramadesai108 (5/2/2011)

    I am trying to store the number in a char field. Wayne has the correct answer. Thanks Wayne, that works.

    I'm glad that it works... but you really shouldn't store a numeric in a character field. Everything you do, you'll have to convert it back to an int first, making everything slow.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • May 2, 2011 at 1:10 pm

    #1318681

    Only if you need to convert it. For rather silly business reasons/vendor application issues, I'm having to do this myself for one of my DB's. I don't care about the conversion, though, as it's only for display purposes.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply