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

    Unanswered: Extract a part of the feild

    I am trying to extracta the begining of a feild. The only this is that there is no set lenght on the characters that I am trying to extract. I am just trying to extract the city and seperate it from the state. This is some of the data that I am trying to do.


    NEW HAMBURG, ONTARIO
    BOLTON, ONTARIO
    WOODRIDGE ONTARIO
    TORONTO, ONTARIO
    BRAMPTON ONTARIO
    GRIMSBY, ONTARIO
    ETOBICOKE, ONTARIO
    LONDON ONTARIO
    AJAX, ONTARIO
    MOSSLEY ONTARIO
    SCARBROUGH ONTARIO
    BRAMPTON ONTARIO

    How would I be able to do this? Anyone have any Ideas. I know I cannot use Left or right in access cause it is not a fixed number of characters.

  2. #2
    Join Date
    Sep 2004
    Location
    Charlotte, NC
    Posts
    164
    You need to have something consistent to look for in detrmining how far over to select for the part of the text string that you want. Because some cities are 2 words you can't use the blank space. I noticed that not every record has a comma after the city, otherwise you could use that. I only saw one consistency in your list and that was that all records were for Ontario. Is everything in Ontario?

    TD

  3. #3
    Join Date
    Jan 2004
    Posts
    164
    Yes. All records are from Ontario. That would probably be the only thing that would be consistent here.

  4. #4
    Join Date
    Oct 2004
    Location
    Emmen, The Netherlands
    Posts
    8
    Well. I don't know a lot about the left/right thingies. But since it's always Ontaria, I guess you could chop down the 8 right characters. I'm not completely sure how though.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    update cities
       set city
         = iif(lcase(right(rtrim(city),9))=', ontario'
            , left(city,len(rtrim(city))-9) ,
           iif(lcase(right(rtrim(city),8))=' ontario'
            , left(city,len(rtrim(city))-8)
            , rtrim(city) ))
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Sep 2004
    Location
    Charlotte, NC
    Posts
    164
    If you want to build a query instead, you can build this into a field where 'Table1' is the table that your address information is currently in and 'FULL_ADDRESS' is the field that your current information is in:

    Left([Table1]![FULL_ADDRESS],IIf(InStr([Table1]![FULL_ADDRESS],",")>0,InStr([Table1]![FULL_ADDRESS],",")-1,InStr([Table1]![FULL_ADDRESS],"ONTARIO")-2))

    This allows for any instance whether there is a comma, a comma and a space, or just a space before you get to Ontario.

    TD
    Last edited by buckeye_td; 10-28-04 at 10:21.

Posting Permissions

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