SitePoint Sponsor

User Tag List

Results 1 to 11 of 11
  1. #1
    SitePoint Zealot
    Join Date
    Feb 2008
    Posts
    165
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Trim after first 5 digits

    I'm looking for some syntax help to remove characters in a string. I have a field called zipcode, which has some different formats in it. I'd like to remove any characters after the first 5.

    for example: 12345-1234
    would return: 12345

    I've looked at TRIM() and RTRIM(), but I don't see how to do the above when I don't know what the sting contains, other than it's length?

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,495
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)

  3. #3
    SitePoint Zealot
    Join Date
    Feb 2008
    Posts
    165
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the link. I see this will return the what I'm looking for.

    What I need to do is trim the field, so the left most characters are removed.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by ggeiger View Post
    ... so the left most characters are removed.
    use SUBSTRING
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Zealot
    Join Date
    Feb 2008
    Posts
    165
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This might be easier than I'm making it, and it's not helping that I just confused things by writing the "left most characters". Sorry about that.

    I have a field called zipcode. We'd like to trim the data in that field, for every record in the DB, to have no more than 5 characters. Preferably, the first five.

    So: 12345-9875
    Would be trimmed down to: 12345

    Apologies if I wasn't clear before!

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    you didn't try the LEFT function yet, as guido suggested?
    Code:
    SELECT LEFT(zipcode,5) ...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Zealot
    Join Date
    Feb 2008
    Posts
    165
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    you didn't try the LEFT function yet, as guido suggested?
    Code:
    SELECT LEFT(zipcode,5) ...
    Thanks, I see how that will return the records. How do I trim them permanently?

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by ggeiger View Post
    How do I trim them permanently?
    i would advise against doing that

    destroying data accuracy is never a good idea
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Zealot
    Join Date
    Feb 2008
    Posts
    165
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    i would advise against doing that

    destroying data accuracy is never a good idea
    I agree that removing good, or accurate data is a bad idea.
    In this case, the "zip +4" data is not only bad data, but is causing the client problems with the USPS and needs to be removed from those records that have extra characters after the first 5.

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    sigh...
    Code:
    UPDATE daTable
       SET zipcode = LEFT(zipcode,5)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Zealot
    Join Date
    Feb 2008
    Posts
    165
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for that!


Bookmarks

Posting Permissions

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