Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2004
    Posts
    164

    Unanswered: Devide 1 feild into 3 feilds!

    Hi there!

    There is a feild in my table that contains the city, state and zip, all in the same feild. I was wondering how would be the best way to devide all that info up into 3 feilds? i really don't know how I would go about it since there are cities that can be comosed of 2 or more words. Here is some sample data. I hope someone can help.

    SOUTH EL MONTE CA91733617
    BOSSIER LA71172
    GARDENA CA90249107
    MILWAUKEE WI53216
    PARIS IL61944
    DUQUOIN IL62832
    REDWOOD FALLS MN56283
    AUBURN ME04210

    IRWINDALE CA91706048
    PORTLAND OR97202901
    PORTLAND OR972028901
    ANAHEIM CA928071735
    KENT WA98032
    CRYSTAL LAKE IL60014611

  2. #2
    Join Date
    Dec 2003
    Location
    Bangalore
    Posts
    28
    Why don't u try to extract from the right end..i.e zip first ..then State and city...since zip and state are single words it should work...

  3. #3
    Join Date
    Dec 2003
    Location
    Bangalore
    Posts
    28
    I have tried to extract ur reuirement....I think this would meet ur requirement...

    create table test(field varchar(100))
    go
    insert into test
    select 'REDWOOD FALLS MN56283'
    select 'SOUTH EL MONTE CA91733617' Union all
    select 'BOSSIER LA71172' Union all
    select 'GARDENA CA90249107'

    go

    select reverse(substring(reverse(field),charindex(' ',reverse(field))+1,len(field))) 'City',
    substring(reverse(substring(reverse(field),1,CHARI NDEX ( ' ' , reverse(field) )-1)),1,2) 'State',
    substring(reverse(substring(reverse(field),1,CHARI NDEX ( ' ' , reverse(field) )-1)),3,len(reverse(substring(reverse(field),1,CHARI NDEX ( ' ' , reverse(field) )-1)))) 'Zip'
    from test


    This code may not me the best performing query...I just wanted the query work...

Posting Permissions

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