Post reply

Splitting Address Data from one field into 3

  • June 23, 2009 at 3:50 pm

    #222948

    Hello,

    I am trying to covert data and I have an address field that has addresses with city, state, and zip all together that look something like

    FORT WAYNE IN 46845

    WEST VALLEY CITY UT 84119-565

    CHICAGO IL 60614-1234

    I need to be able to get the city, state, zip code into their own respective columns. As you can see, I am dealing with inconsistent data so I am finding that using charindex() and substrings are difficult.

    Any advice would be appreciated.

  • June 23, 2009 at 5:03 pm

    #1014346

    Give the following a try. If not completely successful, let us know what issues you run into.

    Code is in the attachment. Once again I just can't seem to post code directly in the thread. Who knows what the problem is, because it is so intermittent. (No I'm not asking if any one knows, it is a rhetorical statement 😉 )

  • June 24, 2009 at 6:49 am

    #1014608

    Hi Lynn,

    Thanks so much for the response. That did work, however, I was able to simplify this for my needs with what you gave me... they key being how you were using the PATINDEX() function. This is what I ended up doing to parse out the data.

    SELECT SUBSTRING(mailingaddr2,1,PATINDEX('%[0-9]%',mailingaddr2)-4) AS city

    SELECT SUBSTRING(mailingaddr2,PATINDEX('%[0-9]%',mailingaddr2)-3,3) AS state

    SELECT SUBSTRING(mailingaddr2,PATINDEX('%[0-9]%',mailingaddr2),5) AS zipcode

    Thanks again for your help!

  • June 24, 2009 at 1:04 pm

    #1014865

    skailey (6/24/2009)

    Hi Lynn,

    Thanks so much for the response. That did work, however, I was able to simplify this for my needs with what you gave me... they key being how you were using the PATINDEX() function. This is what I ended up doing to parse out the data.

    SELECT SUBSTRING(mailingaddr2,1,PATINDEX('%[0-9]%',mailingaddr2)-4) AS city

    SELECT SUBSTRING(mailingaddr2,PATINDEX('%[0-9]%',mailingaddr2)-3,3) AS state

    SELECT SUBSTRING(mailingaddr2,PATINDEX('%[0-9]%',mailingaddr2),5) AS zipcode

    Thanks again for your help!

    As long as you're sure that the city will never contain a number. Don't forget that there are valid state / zip codes for military and US Possessions (Guam, Virgin Islands, etc.)

    Also, you might want to consider changing the state line to only grab the 2 characters, vs. 3:

    SELECT SUBSTRING(mailingaddr2,PATINDEX('%[0-9]%',mailingaddr2)-3, 2) AS state

    If you have to deal with Canadian postal codes, you have a whole different format to be concerned about.

    Edit: added military, canadian info.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

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

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