Professional Web Applications Themes

Converting City/State field - Microsoft SQL / MS SQL Server

I'm trying to convert a combined City/State field in one table into separate City and State fields in a destination table using the following method. Let's say that: Table1.CityState = "Columbus, OH" and I want: Table2.City = "Columbus" Table2.State = "OH" so I'm trying: INSERT INTO Table1 (City, State) SELECT Left([CityState], (Len([CityState]) - 4)), Right([CityState], 2) This works for the most part, but fails if the CityState field is less that four characters. This is from an old database, and some of the records have unusual data in this field. I would use WHERE Len([CityState]) >=4, but I need to ...

  1. #1

    Default Converting City/State field

    I'm trying to convert a combined City/State field in one table into separate
    City and State fields in a destination table using the following method.

    Let's say that:
    Table1.CityState = "Columbus, OH"

    and I want:
    Table2.City = "Columbus"
    Table2.State = "OH"

    so I'm trying:
    INSERT INTO Table1 (City, State)
    SELECT Left([CityState], (Len([CityState]) - 4)), Right([CityState], 2)

    This works for the most part, but fails if the CityState field is less that
    four characters. This is from an old database, and some of the records have
    unusual data in this field. I would use WHERE Len([CityState]) >=4, but I
    need to import all the records. There are many other fields involved, I
    simplified the statement for clarity.

    Any help would be appreciated.

    Andy



    Andy Guest

  2. #2

    Default Re: Converting City/State field

    Try:

    insert Table2
    select
    left (CityState, charindex (', ', CityState) - 1)
    , right (CityState, 2)
    from
    Table1

    --
    Tom

    ---------------------------------------------------------------
    Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
    SQL Server MVP
    Columnist, SQL Server Professional
    Toronto, ON Canada
    www.pinnaclepublishing.com/sql


    "Andy Williams" <com> wrote in message news:O$phx.gbl...
    I'm trying to convert a combined City/State field in one table into separate
    City and State fields in a destination table using the following method.

    Let's say that:
    Table1.CityState = "Columbus, OH"

    and I want:
    Table2.City = "Columbus"
    Table2.State = "OH"

    so I'm trying:
    INSERT INTO Table1 (City, State)
    SELECT Left([CityState], (Len([CityState]) - 4)), Right([CityState], 2)

    This works for the most part, but fails if the CityState field is less that
    four characters. This is from an old database, and some of the records have
    unusual data in this field. I would use WHERE Len([CityState]) >=4, but I
    need to import all the records. There are many other fields involved, I
    simplified the statement for clarity.

    Any help would be appreciated.

    Andy




    Tom Guest

  3. #3

    Default Re: Converting City/State field

    Where does 4 come from?

    How about

    SELECT Left(CityState, CHARINDEX(',', CityState))

    Assuming none of your cities have ',' in them.




    "Andy Williams" <com> wrote in message
    news:O$phx.gbl... 
    separate 
    2) 
    that 
    have 


    Aaron Guest

  4. #4

    Default Re: Converting City/State field

    Whoops, I forgot the -1 ... must be absent-minded (I'm coming to Canada
    tomorrow, so a bit distracted) :P




    "Tom Moreau" <spam.me.cips.ca> wrote in message
    news:phx.gbl...
    Try:

    insert Table2
    select
    left (CityState, charindex (', ', CityState) - 1)
    , right (CityState, 2)
    from
    Table1



    Aaron Guest

  5. #5

    Default Re: Converting City/State field

    This works, but still fails if there's no comma.


    "Aaron Bertrand - MVP" <com> wrote in message
    news:phx.gbl... 


    Andy Guest

  6. #6

    Default Re: Converting City/State field

    How about:

    insert Table2
    select
    left (CityState, charindex (
    case when charindex (', ', CityState) = 0 then ' ' else ', ' end, CityState) - 1)
    , right (CityState, 2)
    from
    Table1

    --
    Tom

    ---------------------------------------------------------------
    Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
    SQL Server MVP
    Columnist, SQL Server Professional
    Toronto, ON Canada
    www.pinnaclepublishing.com/sql


    "Andy Williams" <com> wrote in message news:phx.gbl...
    This works, but still fails if there's no comma.


    "Aaron Bertrand - MVP" <com> wrote in message
    news:phx.gbl... 



    Tom Guest

  7. #7

    Default Re: Converting City/State field

    Damn you're good!

    But, it still fails if there is no space (if they forgot to enter a state or something)


    "Tom Moreau" <spam.me.cips.ca> wrote in message news:Oik%phx.gbl...
    How about:

    insert Table2
    select
    left (CityState, charindex (
    case when charindex (', ', CityState) = 0 then ' ' else ', ' end, CityState) - 1)
    , right (CityState, 2)
    from
    Table1

    --
    Tom

    ---------------------------------------------------------------
    Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
    SQL Server MVP
    Columnist, SQL Server Professional
    Toronto, ON Canada
    www.pinnaclepublishing.com/sql


    "Andy Williams" <com> wrote in message news:phx.gbl...
    This works, but still fails if there's no comma.


    "Aaron Bertrand - MVP" <com> wrote in message
    news:phx.gbl... 


    Andy Guest

  8. #8

    Default Re: Converting City/State field

    >> But, it still fails if there is no space (if they forgot to enter a state
    or something)

    So, what are you expecting to go into the state column in this case?


    Aaron Guest

  9. #9

    Default Re: Converting City/State field

    Nothing...

    I know, it's a joke! Garbage in, garbage out!
    "Aaron Bertrand - MVP" <com> wrote in message
    news:phx.gbl... [/ref][/ref]
    state 


    Andy Guest

  10. #10

    Default Re: Converting City/State field

    So, adapting Tom's cleverly nested CHARINDEX...

    SELECT
    LEFT(CityState,CHARINDEX(CASE WHEN CHARINDEX (', ', CityState) > 0 THEN ',
    ' ELSE ' ' END, CityState) - 1), CASE WHEN CHARINDEX(', ', CityState) > 0
    THEN RIGHT(CityState, 2) ELSE '' END
    FROM Table1



    "Andy Williams" <com> wrote in message
    news:Ol#phx.gbl... [/ref]
    > state 
    >
    >[/ref]


    Aaron Guest

  11. #11

    Default Re: Converting City/State field

    That's getting closer. Still doesn't quite get the job done. It still
    fails if CityState contains no spaces. I think its because charindex is
    returning null. So this would cause the failure if the user entered
    "Columbus", "ColumbusOH", or "Columbus,OH". All three of these scenarios
    are in there somewhere.

    I do understand your logic though, and I think I can get it from here.

    But, if you get bored and want to plug away at it some more, feel free.

    Thanks guys...


    "Aaron Bertrand - MVP" <com> wrote in message
    news:%phx.gbl... 
    ', 
    > > state 
    > >
    > >[/ref]
    >
    >[/ref]


    Andy Guest

  12. #12

    Default Re: Converting City/State field

    Makes you wonder what to do with 'AUSTIN'. Does that translate to AUSTIN or AUST IN, the city of Aust in Indiana.

    --
    Tom

    ---------------------------------------------------------------
    Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
    SQL Server MVP
    Columnist, SQL Server Professional
    Toronto, ON Canada
    www.pinnaclepublishing.com/sql


    "Andy Williams" <com> wrote in message news:phx.gbl...
    That's getting closer. Still doesn't quite get the job done. It still
    fails if CityState contains no spaces. I think its because charindex is
    returning null. So this would cause the failure if the user entered
    "Columbus", "ColumbusOH", or "Columbus,OH". All three of these scenarios
    are in there somewhere.

    I do understand your logic though, and I think I can get it from here.

    But, if you get bored and want to plug away at it some more, feel free.

    Thanks guys...


    "Aaron Bertrand - MVP" <com> wrote in message
    news:%phx.gbl... 
    ', 
    > > state 
    > >
    > >[/ref]
    >
    >[/ref]



    Tom Guest

  13. #13

    Default Re: Converting City/State field

    I wouldn't be surprised if it said "Monkeytube". But hey, its a paycheck...


    "Andy Williams" <com> wrote in message
    news:phx.gbl... [/ref]
    THEN [/ref]
    0 [/ref][/ref]

    > >
    > >[/ref]
    >
    >[/ref]


    Andy Guest

  14. #14

    Default Re: Converting City/State field

    The requirement seemed to be, if there is no space, then state was left out.
    ;)


    "raydan" <nospamcom> wrote in message
    news:e$K$phx.gbl... 


    Aaron Guest

  15. #15

    Default Re: Converting City/State field

    My example assumed Austin, or Columbusoh, or Providenceri, or maybe even
    Torontoon. :-)



    "Tom Moreau" <spam.me.cips.ca> wrote in message
    news:phx.gbl...
    Makes you wonder what to do with 'AUSTIN'. Does that translate to AUSTIN or
    AUST IN, the city of Aust in Indiana.



    Aaron Guest

  16. #16

    Default Re: Converting City/State field

    Yeah. Then there's Miami and Philadelphia, which suddenly end up in Michigan and Iowa. ;-)

    --
    Tom

    ---------------------------------------------------------------
    Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
    SQL Server MVP
    Columnist, SQL Server Professional
    Toronto, ON Canada
    www.pinnaclepublishing.com/sql


    "Aaron Bertrand - MVP" <com> wrote in message news:phx.gbl...
    My example assumed Austin, or Columbusoh, or Providenceri, or maybe even
    Torontoon. :-)



    "Tom Moreau" <spam.me.cips.ca> wrote in message
    news:phx.gbl...
    Makes you wonder what to do with 'AUSTIN'. Does that translate to AUSTIN or
    AUST IN, the city of Aust in Indiana.




    Tom Guest

  17. #17

    Default Re: Converting City/State field

    Well, there has been so many "IFs" since the original question that I'm
    having trouble keeping track of the requirements.
    Give me a break, I just got back from vacation. :-(

    "Aaron Bertrand - MVP" <com> wrote in message
    news:phx.gbl... 
    out. 
    >
    >[/ref]


    raydan Guest

  18. #18

    Default Re: Converting City/State field

    Hey, *you* don't get to make jokes about this thread, only we do!

    <g>


    "Andy Williams" <com> wrote in message
    news:phx.gbl... 


    Aaron Guest

  19. #19

    Default Re: Converting City/State field

    Who's "WE" ?

    "Aaron Bertrand - MVP" <com> wrote in message
    news:phx.gbl... 
    >
    >[/ref]


    raydan Guest

  20. #20

    Default Re: Converting City/State field

    Anyone who /isn't/ coming up with 18 different "what if" scenarios after the
    fact. :-)

     


    Aaron Guest

Page 1 of 2 12 LastLast

Similar Threads

  1. Country, State, City, Postal/Zip Code info
    By baz in forum Coldfusion - Advanced Techniques
    Replies: 0
    Last Post: June 9th, 07:47 PM
  2. Converting a text field to a date field - FM6
    By Carl Mittler in forum FileMaker
    Replies: 2
    Last Post: October 17th, 09:38 PM
  3. Replies: 5
    Last Post: August 14th, 05:11 PM
  4. state (Field Problem)
    By Louie Miranda in forum PHP Development
    Replies: 1
    Last Post: July 17th, 06:30 AM
  5. City/State auto fill-in with entry of ZipCode
    By John Vinson in forum Microsoft Access
    Replies: 2
    Last Post: July 8th, 02:20 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139