Post reply

How can i split up the zipcode and city from my table?

  • June 11, 2009 at 3:37 pm

    #131107

    How can i split up the zipcode and city from my table in a query?

    my table has the zipcode and city in the same column

    the zipcode from the left has 3 numbers a space then 2 numbers a space then the city

    the goal is to get one column: zip and one column city

    example.

    select * from crm5.CAD3 gives (one column):

    [CAD3]

    151 85 SÖDERTÄLJE

    421 23 VÄSTRA FRÖLUNDA

    161 84 BROMMA

    385 25 TORSÅS

    Goal:

    [ZIP][City]

    151 85 SÖDERTÄLJE

    421 23 VÄSTRA FRÖLUNDA

    161 84 BROMMA

    385 25 TORSÅS

    acceptable goal:

    [ZIP][City]

    15185 SÖDERTÄLJE

    42123 VÄSTRA FRÖLUNDA

    16184 BROMMA

    38525 TORSÅS

  • June 11, 2009 at 6:15 pm

    #1008326

    select

    [ZIP] = replace(left([CAD3],6), ' ', ''),

    [City] = substring([CAD3], 7, len([CAD3]))

    from crm5.CAD3

    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

  • June 11, 2009 at 7:09 pm

    #1008337

    Thank you Wayne. Thanks to you I even figured out how to retain the space in the swedish zip by adapting your script like this:

    select

    [ZIP] = replace(left([CAD3],6), '', ''),

    [City] = substring([CAD3], 7, len([CAD3]))

    from crm5.[supoffrcm-nsi]

    Thanks again, Steve

  • June 12, 2009 at 10:55 am

    #1008814

    Steve Kinnaman (6/11/2009)

    Thank you Wayne. Thanks to you I even figured out how to retain the space in the swedish zip by adapting your script like this:

    select

    [ZIP] = replace(left([CAD3],6), '', ''),

    [City] = substring([CAD3], 7, len([CAD3]))

    from crm5.[supoffrcm-nsi]

    Thanks again, Steve

    If you want to keep the space in the zip code you don't need the replace function. Currently, you're replacing '' with '', which is equal to "replace empty string with empty string".

    Just change it to [ZIP] = left([CAD3],6),

    Edit: typo fixed.


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • June 12, 2009 at 5:32 pm

    #1008948

    Steve Kinnaman (6/11/2009)

    Thank you Wayne. Thanks to you I even figured out how to retain the space in the swedish zip by adapting your script like this:

    select

    [ZIP] = replace(left([CAD3],6), '', ''),

    [City] = substring([CAD3], 7, len([CAD3]))

    from crm5.[supoffrcm-nsi]

    Thanks again, Steve

    Steve,

    Your desired output did not show a space, so I removed it.

    the Code with keeping the space in the zip would be:

    select

    [ZIP] = left([CAD3],6),

    [City] = substring([CAD3], 7, len([CAD3]))

    from crm5.[supoffrcm-nsi]

    Edit: Whoops! Just saw a "Goal" and an "Acceptable Goal" area... I missed the "Goal" somehow for the first post.

    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 5 posts - 1 through 4 (of 4 total)

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