• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 278
  • Last Modified:

Parsing City and State From One Field

I've read all of the post and I don't think this one has been answered.  I have a field that has CITY ST.  I need to break them out into two fields.  No commas just a space between CITY and ST.  I've already done the ST as that was the easy part.  Also, the solution needs to be something I can put into a query.  Thanks for your help.
0
DocB1973
Asked:
DocB1973
  • 3
  • 2
1 Solution
 
Rey Obrero (Capricorn1)Commented:

try this query

select field,left([field],instr([field]," ")-1) as City, mid([field],instr([field]," ")+1) as State
from tableName
0
 
DocB1973Author Commented:
Thank for the quick reply.  It handles everything except when the city has more than one name.  Exp: Grand Rapids MI.
0
 
Rey Obrero (Capricorn1)Commented:

ok try this

select field,left([field],instrrev([field]," ")-1) as City, mid([field],instrrev([field]," ")+1) as State
from tableName




for other fomats you will then need a UDF to do the parsing
0
 
DocB1973Author Commented:
Fantasic!  Works like a charm.
0
 
DocB1973Author Commented:
Great job for something you think you know how to do but it turns out it's more difficult than you think.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now