Post reply

Adding Leading Zeros to Integer datatype

  • September 9, 2016 at 10:56 am

    #329955

    When I insert data, it is cutting of leading zeros as the column I am inserting is of 'int' datatype.

    Is there way to insert leading zeros to a integer datatype column?

  • September 9, 2016 at 10:58 am

    #1900117

    No, because leading zeros are meaningless on numeric data. If you have something that should have leading zeros, it should be stored as a string, because it's not numeric data.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • September 9, 2016 at 11:05 am

    #1900118

    Or you should leave it as INT and format your output such that it displays in the format you require.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • September 9, 2016 at 1:11 pm

    #1900150

    Little known, little used, and you should not use this until you are displaying data or outputting it to a client program.

    declare @int int = 123

    select FORMAT(@int,'0#########')

    I agree 100% that integers should be stored as integers and NOT as character strings.

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

  • September 9, 2016 at 1:47 pm

    #1900156

    Actually, I think the FORMAT function has been shown to be unnecessarily expensive and should be avoided altogether.

    FORMAT() is nice and all, but…

    There have also been a few threads here on SSC where posters are getting equally poor results...

    I'd just stick with the tried and true...

    DECLARE @int INT = 123;

    SELECT RIGHT(CONCAT('000000', CAST(@int AS VARCHAR(6))), 6)

  • September 9, 2016 at 2:32 pm

    #1900166

    Thanks Jason. I never format data in SQL itself, so I had no idea about the performance hit. But I just tested it for half a million rows against your code and it REALLY drags.

    Forget my suggestion, go with Jason's. But ONLY when you are returning data to a calling program.

    Once you make it a string you are complicating all further calculations and comparisons.

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

  • September 9, 2016 at 3:41 pm

    #1900182

    The Dixie Flatline (9/9/2016)

    Thanks Jason. I never format data in SQL itself, so I had no idea about the performance hit. But I just tested it for half a million rows against your code and it REALLY drags.

    Forget my suggestion, go with Jason's. But ONLY when you are returning data to a calling program.

    Once you make it a string you are complicating all further calculations and comparisons.

    Just to confirm, the simple testing I did with FORMAT vs CONVERT showed that FORMAT was 44 times slower than CONVERT.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • September 10, 2016 at 5:59 am

    #1900210

    FORMAT or CONVERT or STR - they all return varchar data type.

    On an attempt to save their outcome into int column it will be converted back to int and all the zeros will be dropped.

    Formatting is relevant only to string presentations of integers in reports, interfaces, etc.

    _____________
    Code for TallyGenerator

Viewing 8 posts - 1 through 7 (of 7 total)

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