Results 1 to 12 of 12
  1. #1
    Join Date
    Mar 2003
    Posts
    10

    Unanswered: Splitting postal codes and citynames

    Hello

    I have a table with a column that contains both postal code and city. But sometimes the postalcode is missing and then there are only the city name in the column.
    I am doing an import from that table to a table where the postal code and city are in different fields.
    So I have to separate the postal code and the city. The postal code is (often) 6 digits long. Now I have a left(postaladdress,6) to get the postal code. But this is not a good solution.

    Do you know how I can take all digits and separate them from all letters. And if there are no digits , do nothing.


    I also have the same problem with separating first name and last name.

    THANKS
    Last edited by soh; 07-23-03 at 10:12.

  2. #2
    Join Date
    Sep 2002
    Location
    Mancos, CO
    Posts
    73

    Re: Splitting postal codes and citynames

    Can you post a small example of your data for both problems?

    Originally posted by soh
    Hello

    I have a table with a column that contains both postal code and city. But sometimes the postalcode is missing and then there are only the city name in the column.
    I am doing an import from that table to a table where the postal code and city are in different fields.
    So I have to separate the postal code and the city. The postal code is (often) 6 digits long. Now I have a left(postaladdress,6) to get the postal code. But this is not a good solution.

    Do you know how I can take all digits and separate them from all letters. And if there are no digits , do nothing.


    I also have the same problem with separating first name and last name.

    THANKS

  3. #3
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    select substring (title_id, 1, patindex('%[0-9]%', title_id) - 1), substring (title_id, patindex('%[0-9]%', title_id), 6)
    from titles
    where title_id like '%[0-9]%'
    union
    select title_id, null
    from titles
    where title_id not like '%[0-9]%'


    I just love the pubs database.

    Hope this helps.

  4. #4
    Join Date
    Mar 2003
    Posts
    10

    Example

    I solved the problem with first name and last name.

    But I didnt solve the postal code problem.

    Here is an example of my data


    Address
    ----------------------------------------
    999 99 CityName

    As you see the postalcode contains a blank after three digits. So how can I split this?

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Then how about

    select case when postaladdress like '%[0-9]%' then left(postaladdress, 6) as postalcode else null end as PostalCode,
    case when postaladdress like '%[0-9]%' then substring(postaladdress, 7, 100) else postaladdress end as CityName
    from table...

  6. #6
    Join Date
    Mar 2003
    Posts
    10

    I didnt worked

    Hi

    Thanks, but I didnt get this piece of code to work. I get "Incorrect syntax near as".

  7. #7
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    select case when postaladdress like '%[0-9]%' then left(postaladdress, 6) else null end as PostalCode,
    case when postaladdress like '%[0-9]%' then substring(postaladdress, 7, 100) else postaladdress end as CityName
    from table...

  8. #8
    Join Date
    Mar 2003
    Posts
    10
    Thanks! Now the clause work in the query analyzer.

    Here is how I planned to use this code. But it doesn't work, I get "Incorrect syntax near 'as'" :

    UPDATE tbl_norm_addresses
    SET
    ChangedBy = @UserIdNo,
    ChangedDate = @Date,
    Address1 = rtrim(NAM.SN_NAME),
    Address2 = rtrim(NAM.SN_ADDR1),
    Address3 = '',
    Address4 = ''
    PostalCode = case when sn_addr2 like '%[0-9]%' then left(sn_addr2, 6) else null end as PostalCode,
    PostalAddress = case when sn_addr2 like '%[0-9]%' then ltrim(rtrim(substring(sn_addr2, 7, 100))) else sn_addr2 end as CityName

    FROM S_NAME NAM JOIN tbl_norm_Customers ON
    rtrim(NAM.SN_ACCOUNT) = rtrim(IsNull(ExternalCustNo,''))

  9. #9
    Join Date
    Mar 2003
    Posts
    10
    Anyone know how to use the code in a update clause as I pasted ??

  10. #10
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    drop the as PostalCode and as CityName
    Paul Young
    (Knowledge is power! Get some!)

  11. #11
    Join Date
    Mar 2003
    Posts
    10
    I tried this, but I get the error "Incorrect syntaxt near Postalcode".

  12. #12
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    Sorry, you also need to add a , after Address4 = ''
    Paul Young
    (Knowledge is power! Get some!)

Posting Permissions

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