Post reply

Determine if String has numbers in datatype varchar(10)

  • March 9, 2009 at 8:21 am

    #206758

    I am trying to clean up our Zip Code table. Most of the time the zip code entry is Zip+4. I have to clean up so that I am only keeping first 5 of the zip. Problem is that many times zip code column also has letter for the applicants outside of the country, so I can't modify them. I am only allowed to clean up zip+4. The logic I am using right now is

    update DimStudent

    set StudentZip = substring(StudentZip, 1, 5)

    where len(StudentZip) = 10 and substring(StudentZip, 6, 1) = '-'

    Problem with this code is that StudentZip doesn't always have Zip+4 (10 characters) and sometimes they don't have a hyphen. Sometimes it has Zip+3 or Zip+2 (I know, weird).

    So I am told that the logic I should be using is: Check if the first first 5 characters are numbers. If they are numbers, then keep just first 5 charaters (or delete everything after 5 characters).

    I checked many website and two books on T-SQL but couldn't find a code that will check to see if a string is number. Please help.

    Thanks in Advance.

    ------------
    🙂

  • March 9, 2009 at 8:27 am

    #956046

    Try

    update DimStudent

    set StudentZip = substring(StudentZip, 1, 5)

    where StudentZip like '[0-9][0-9][0-9][0-9][0-9]%'

    Madhivanan

    Failing to plan is Planning to fail

  • March 9, 2009 at 8:27 am

    #956047

    Where MyZipColumn like '[0-9][0-9][0-9][0-9][0-9]%'

    Will find anything that is five numbers, followed by anything or nothing.

    Where MyZipColumn like '[0-9][0-9][0-9][0-9][0-9]'

    or MyZipColumn like '[0-9][0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]'

    or MyZipColumn like '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'

    Will find anything that's five numbers and nothing else, or anything that's five numbers followed by a hyphen and then four numbers, or anything that's nine numbers (five and four) without a hyphen.

    The braces with a range in them select anything that matches that range. In this case, any single digit that's between 0 and 9.

    Does that help?

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • March 9, 2009 at 8:28 am

    #956049

    It goes LIKE this:

    SELECT

    StudentZip,

    SUBSTRING(StudentZip, 1, 5) AS CleanedZip

    FROM DimStudents

    WHERE (StudentZip LIKE '[0-9][0-9][0-9][0-9][0-9]%')

  • March 9, 2009 at 8:37 am

    #956060

    Thanks a lot guys. It worked.

    ------------
    🙂

  • March 10, 2009 at 1:32 am

    #956598

    Rav (3/9/2009)

    Thanks a lot guys. It worked.

    You are welcome 🙂

    Madhivanan

    Failing to plan is Planning to fail

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

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