Page 1 of 2 12 LastLast
Results 1 to 15 of 17

Thread: Truncate characters in a column

  1. #1
    Join Date
    Aug 2005
    Posts
    31

    Truncate characters in a column

    I recieve data where I am comparing zip codes from raw data, to a zip code table. The columns I am matching are in integers.

    The issue is that in the raw data, sometimes the zip code is extended to more than the 5 characters in the zip code table so I am getting null value returns

    A sample of this is in the raw data is:

    Zipcode
    54303
    54304786
    78641

    Is there a way I can have it compare only the first 5 digits of the zip codes in the raw data zip code column to the zip code table? Or would I have to convert the data type? Or alter or truncate the characters somehow? If so, please include how to do this. Any help would be greatly appreciated. Thank you!

  2. #2
    Join Date
    Sep 2002
    Posts
    169
    Have a look at the LEFT function.

    e.g. LEft (ZipCode, 5) returns the first 5 characters

  3. #3
    Join Date
    Aug 2005
    Posts
    31

    Truncate characters in a column

    Stephen,
    Thank you! Can I use that with an UPDATE statement?

    like UPDATE LEFT (ZipCode, 5) ?

  4. #4
    Join Date
    Sep 2002
    Posts
    169
    Yes
    Update mytable set ZipCode = Left(LongZipCode, 5)

    Or

    Update ZipCodeTable
    set ZipCode = Left(LongZipCode, 5)
    From ZipCodeTable Z
    Inner Join ZipCodeRaw R

  5. #5
    Join Date
    Sep 2002
    Posts
    169
    Oops - accidentally hit enter before I finished typing....

    Yes
    Update mytable set ZipCode = Left(LongZipCode, 5)

    Or

    Update ZipCodeTable
    set ZipCode = Left(LongZipCode, 5)
    From ZipCodeTable Z
    Inner Join ZipCodeRaw R
    On Z.ZipCode = Left(R.LongZipCode, 5)

  6. #6
    Join Date
    Aug 2005
    Posts
    31

    Smile

    Thanks again Stephen!
    I will give this a shot in the morning and post how it works out.
    I really appreciate your expertise on this.

    Kudos to this site and it's readers. I seem to get more responsiveness and quality solutions when I post questions here than from the "developers" in my group at work. I am glad this site exists.

  7. #7
    Join Date
    Aug 2005
    Posts
    31
    I tried using LEFT, but it seems to want to chop off zeros if there is one leading the zip code.

    For example 076312140 was changed to 76312, 034353101
    was changed to 34353 and seems to be causing null value returns. Is there any way to prevent this, or something else I can try? Thanks again

  8. #8
    Join Date
    Sep 2002
    Posts
    5,938
    Try convert it to char(5).

  9. #9
    Join Date
    Aug 2005
    Posts
    31
    What would that syntax look like? I am still fairly new to this, so pardon my ignorance

  10. #10
    Join Date
    Sep 2002
    Posts
    5,938
    select convert(char(5), '034353101')

  11. #11
    Join Date
    Aug 2005
    Posts
    31
    Is there a means of specifying a column to convert like that?
    I have about 1000 zip codes that are too long. Thanks for your help

  12. #12
    Join Date
    Sep 2002
    Posts
    5,938
    Can try:

    update table set zipcode = convert(char(5), longzipcode)

    or

    update table set zipcode = substring(longzipcode, 1, 5)

  13. #13
    Join Date
    Aug 2005
    Posts
    31
    That did it. Thanks for your help!

  14. #14
    Join Date
    Aug 2005
    Posts
    31
    Just curious on what data type a zip code should be use in a sql table. It was suggested at work that I use integer, that is what was knocking the leading zero off of the zip code.
    Should this be char? Thanks again all

  15. #15
    Join Date
    Sep 2002
    Posts
    5,938
    Better to use char(5) if you only need 5 digit zip number.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •