Post reply

Need to Keep the Leading zero

  • January 13, 2014 at 6:08 am

    #288315

    Hello Everyone

    I hope that you all had a nice weekend.

    I am processing some data thru for cleansing. All the data comes in as text. I have some that looks like this

    00158445408333

    I need to change the data type to more of the exact, but I need to ensure that the leading zeros are preserved. I have tried decimal(18,0), float, numeric(18,) but everything that I select removes the leading zeros. What data type would I be able to use to preserve the leading zeros?

    Thank you in advance for all your assistance, suggestions and comments.

    Andrew SQLDBA

  • January 13, 2014 at 6:12 am

    #1679555

    Varchar.

    If the leading 0 is important, then it's not a number. Numbers do not have significant leading 0s, we don't write 0100 for one hundred. We write 0120 for postal codes, which are strings.

    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

  • January 13, 2014 at 6:28 am

    #1679559

    Thanks Gail

    I never thought of it that way. Great analogy. While looking thru some of the data, I found that one out of the 3 million rows actually had a character in the string.

    Something like "0123456789W0238"

    Thanks again

    Andrew SQLDBA

Viewing 3 posts - 1 through 2 (of 2 total)

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