Parse usable Street Address City State Zip from a string

Question

Problem  I have an address field from an Access database which has been converted to SQL Server 2005  This field has everything all in one field  I need to parse out the address s individual sections into their appropriate fields in a normalized table  I need to do this for approximately 4 000 records  and it needs to be repeatable  Assumptions   Assume an address in the US  for now   assume that the input string will sometimes contain an addressee  the person being addressed  and or a second street address  i e  Suite B   states may be abbreviated  zip code could be standard 5 digits or zip 4  there are typos in some instances   UPDATE  In response to the questions posed  standards were not universally followed  I need need to store the individual values  not just geocode and errors means typo  corrected above  Sample Data   A  P  Croll  amp  Son  2299 Lewes-Georgetown Hwy  Georgetown  DE 19947  11522 Shawnee Road  Greenwood DE  19950  144 Kings Highway  S W  Dover  DE  19901  Intergrated Const  Services 2 Penns Way Suite 405 New Castle  DE  19720  Humes Realty 33 Bridle Ridge Court  Lewes  DE  19958  Nichols Excavation 2742 Pulaski Hwy Newark  DE  19711  2284 Bryn Zion Road  Smyrna  DE  19904  VEI Dover Crossroads  LLC  1500 Serpentine Road  Suite 100 Baltimore MD  21  580 North Dupont Highway Dover  DE  19901  P O  Box 778 Dover  DE  19903

User · Accepted Answer

I ve done a lot of work on this kind of parsing  Because there are errors you won t get 100  accuracy  but there are a few things you can do to get most of the way there  and then do a visual BS test  Here s the general way to go about it  It s not code  because it s pretty academic to write it  there s no weirdness  just lots of string handling    Now that you ve posted some sample data  I ve made some minor changes    Work backward  Start from the zip code  which will be near the end  and in one of two known formats  XXXXX or XXXXX-XXXX  If this doesn t appear  you can assume you re in the city  state portion  below  The next thing  before the zip  is going to be the state  and it ll be either in a two-letter format  or as words  You know what these will be  too -- there s only 50 of them  Also  you could soundex the words to help compensate for spelling errors  before that is the city  and it s probably on the same line as the state  You could use a zip-code database to check the city and state based on the zip  or at least use it as a BS detector  The street address will generally be one or two lines  The second line will generally be the suite number if there is one  but it could also be a PO box  It s going to be near-impossible to detect a name on the first or second line  though if it s not prefixed with a number  or if it s prefixed with an  attn   or  attention to   it could give you a hint as to whether it s a name or an address line    I hope this helps somewhat

User · Answer

For  ruby or rails developers there is a nice gem available called street address   I have been using this on one of my project and it does the work I need   The only Issue I had was whenever an address is in this format P  O  Box 1410 Durham  NC 27702  it returned nil and therefore I had to replace  P  O  Box  with    and after this it were able to parse it

User · Answer

Since there is chance of error in word  think about using SOUNDEX combined with LCS algorithm to compare strings  this will help a lot

User · Answer

Another request for sample data   As has been mentioned I would work backwards from the zip   Once you have a zip I would query a zip database  store the results  and remove them  amp  the zip from the string    That will leave you with the address mess  MOST  All   addresses will start with a number so  find the first occurrence of a number in the remaining string and grab everything from it to the  new  end of the string  That will be your address  Anything to the left of that number is likely an addressee   You should now have the City  State   amp  Zip stored in a table and possibly two strings  addressee and address  For the address  check for the existence of  Suite  or  Apt   etc  and split that into two values  address lines 1  amp  2     For the addressee I would punt and grab the last word of that string as the last name and put the rest into the first name field  If you don t want to do that  you ll need to check for salutation  Mr   Ms   Dr   etc   at the start and make some assumptions based on the number of spaces as to how the name is made up    I don t think there s any way you can parse with 100  accuracy

User · Answer

RecogniContact is a Windows COM object that parses US and European addresses  You can try it right on  http   www loquisoft com index php page 8

User · Answer

This won t solve your problem  but if   you only needed lat long data for   these addresses  the Google Maps API   will parse non-formatted addresses   pretty well    Good suggestion  alternatively you can execute a CURL request for each address to Google Maps and it will return the properly formatted address   From that  you can regex to your heart s content

User · Answer

The original poster has likely long moved on  but I took a stab at porting the Perl Geo  StreetAddress US module used by geocoder us to C   dumped it on CodePlex  and think that people stumbling across this question in the future may find it useful   US Address Parser  On the project s home page  I try to talk about its  very real  limitations  Since it is not backed by the USPS database of valid street addresses  parsing can be ambiguous and it can t confirm nor deny the validity of a given address  It can just try to pull data out from the string   It s meant for the case when you need to get a set of data mostly in the right fields  or want to provide a shortcut to data entry  letting users paste an address into a textbox rather than tabbing among multiple fields   It is not meant for verifying the deliverability of an address   It doesn t attempt to parse out anything above the street line  but one could probably diddle with the regex to get something reasonably close--I d probably just break it off at the house number

User · Answer

If it s human entered data  then you ll spend too much time trying to code around the exceptions   Try    Regular expression to extract the zip code Zip code lookup  via appropriate government DB  to get the correct address Get an intern to manually verify the new data matches the old

User · Answer

There are data services that given a zip code will give you list of street names in that zip code   Use a regex to extract Zip or City State - find the correct one or if a error get both  pull the list of streets from a data source Correct the city and state  and then street address  Once you get a valid Address line 1  city  state  and zip you can then make assumptions on address line 2  3

User · Answer

using google API   d str replace            address url    completeurl   http   maps googleapis com maps api geocode xml address    d   amp sensor true     phpobject   simplexml load file  completeurl   print r  phpobject

User · Answer

This won t solve your problem  but if you only needed lat long data for these addresses  the Google Maps API will parse non-formatted addresses pretty well

User · Answer

Are there any standards at all in the way that the addresses are recorded  For example     Are there always commas or new-lines separating street1 from street2 from city from state from zip   Are address types  road  street  boulevard  etc  always spelled out  always abbreviated  Some of each   Define  error      My general answer is a series of Regular Expressions  though the complexity of this depends on the answer  And if there is no consistency at all  then you may only be able to achieve partial success with a Regex  ie  filtering out zip code and state  and will have to do the rest by hand  or at least go through the rest very carefully to make sure you spot the errors

User · Answer

1 on James A  Rosen s suggested solution as it has worked well for me  however for completists this site is a fascinating read and the best attempt I ve seen in documenting addresses worldwide  http   www columbia edu kermit postal html

User · Answer

I think outsourcing the problem is the best bet  send it to the Google  or Yahoo  geocoder   The geocoder returns not only the lat long  which aren t of interest here   but also a rich parsing of the address  with fields filled in that you didn t send  including ZIP 4 and county    For example  parsing  1600 Amphitheatre Parkway  Mountain View  CA  yields       name    1600 Amphitheatre Parkway  Mountain View  CA  USA      Status          code   200       request    geocode          Placemark                  address    1600 Amphitheatre Pkwy  Mountain View  CA 94043  USA          AddressDetails              Country                CountryNameCode    US              AdministrativeArea                  AdministrativeAreaName    CA                SubAdministrativeArea                    SubAdministrativeAreaName    Santa Clara                  Locality                      LocalityName    Mountain View                    Thoroughfare                        ThoroughfareName    1600 Amphitheatre Pkwy                                      PostalCode                        PostalCodeNumber    94043                                                                                  Accuracy   8                 Point              coordinates    -122 083739  37 423021  0                        Now that s parseable

User · Answer

There is javascript port of perl Geo  StreetAddress  US package  https   github com hassansin parse-address   It s regex-based and works fairly well

User · Answer

This type of problem is hard to solve because of underlying ambiguities in the data   Here is a Perl based solution that defines a recursive descent grammar tree based on regular expressions to parse many valid combination of street addresses  http   search cpan org  kimryan Lingua-EN-AddressParse-1 20 lib Lingua EN AddressParse pm   This includes sub properties within an address such as  12 1st Avenue N Suite   2 Somewhere CA 12345 USA  It  is similar to http   search cpan org  timb Geo-StreetAddress-US-1 03 US pm mentioned above  but also works for addresses that are not from the USA  such as the UK  Australia and Canada   Here is the output for one of your sample addresses  Note that the name section would need to be removed first from  A  P  Croll  amp  Son 2299 Lewes-Georgetown Hwy  Georgetown  DE 19947  to reduce it to  2299 Lewes-Georgetown Hwy  Georgetown  DE 19947   This is easily achieved by removing all data up to the first number found in the string   Non matching part          Error                    0  Error descriptions         Case all                 2299 Lewes-Georgetown Hwy Georgetown DE 19947  COMPONENTS                 country                    po box type                post box                   post code                19947  pre cursor                 property identifier      2299  property name              road box                   street                   Lewes-Georgetown  street direction           street type              Hwy  sub property identifier    subcountry               DE  suburb                   Georgetown

User · Answer

You might want to check this out   http   jgeocoder sourceforge net parser html Worked like a charm for me

User · Answer

Based on the sample data    I would start at the end of the string   Parse a Zip-code  either format    Read end to first space  If no Zip Code was found Error  Trim the end then for spaces and special chars  commas  Then move on to State  again use the Space as the delimiter   Maybe use a lookup list to validate 2 letter state codes  and full state names   If no valid state found  error  Trim spaces and commas from the end again  City gets tricky  I would actually use a comma here  at the risk of getting too much data in the city   Look for the comma  or beginning of the line  If you still have chars left in the string  shove all of that into an address field    This isn t perfect  but it should be a pretty good starting point

User · Answer

After the advice here  I have devised the following function in VB which creates passable  although not always perfect  if a company name and a suite line are given  it combines the suite and city  usable data  Please feel free to comment refactor yell at me for breaking one of my own rules  etc     Public Function parseAddress ByVal input As String  As Collection     input   input Replace              input   input Replace                Dim splitString   As String   Split input      Dim streetMarker   As String   New String     street    st    st     avenue    ave    ave     blvd    blvd     highway    hwy    hwy     box    road    rd    rd     lane    ln    ln     circle    circ    circ     court    ct    ct        Dim address1 As String     Dim address2 As String          Dim city As String     Dim state As String     Dim zip As String     Dim streetMarkerIndex As Integer      zip   splitString splitString Length - 1  ToString       state   splitString splitString Length - 2  ToString       streetMarkerIndex   getLastIndexOf splitString  streetMarker    1     Dim sb As New StringBuilder      For counter As Integer   streetMarkerIndex To splitString Length - 3         sb Append splitString counter             Next counter     city   RTrim sb ToString        Dim addressIndex As Integer   0      For counter As Integer   0 To streetMarkerIndex         If IsNumeric splitString counter                 Or splitString counter  ToString ToLower    po                Or splitString counter  ToString   ToLower   Replace             po  Then                 addressIndex   counter             Exit For         End If     Next counter      sb   New StringBuilder     For counter As Integer   addressIndex To streetMarkerIndex - 1         sb Append splitString counter             Next counter      address1   RTrim sb ToString         sb   New StringBuilder      If addressIndex   0 Then         If splitString splitString Length - 2  ToString    lt  gt  splitString streetMarkerIndex   1  Then             For counter As Integer   streetMarkerIndex To splitString Length - 2                 sb Append splitString counter                     Next counter         End If     Else         For counter As Integer   0 To addressIndex - 1             sb Append splitString counter                 Next counter     End If     address2   RTrim sb ToString         Dim output As New Collection     output Add address1   Address1       output Add address2   Address2       output Add city   City       output Add state   State       output Add zip   Zip       Return output End Function  Private Function getLastIndexOf ByVal sArray As String    ByVal checkArray As String    As Integer     Dim sourceIndex As Integer   0     Dim outputIndex As Integer   0     For Each item As String In checkArray         For Each source As String In sArray             If source ToLower   item ToLower Then                 outputIndex   sourceIndex                 If item ToLower    box  Then                     outputIndex   outputIndex   1                 End If             End If             sourceIndex   sourceIndex   1         Next         sourceIndex   0     Next     Return outputIndex End Function   Passing the parseAddress function  A  P  Croll  amp  Son  2299 Lewes-Georgetown Hwy  Georgetown  DE 19947  returns    2299 Lewes-Georgetown Hwy A  P  Croll  amp  Son   Georgetown DE 19947

User · Answer

I ve done this in the past   Either do it manually   build a nice gui that helps the user do it quickly  or have it automated and check against a recent address database  you have to buy that  and manually handle errors    Manual handling will take about 10 seconds each  meaning you can do 3600 10   360 per hour  so 4000 should take you approximately 11-12 hours  This will give you a high rate of accuracy   For automation  you need a recent US address database  and tweak your rules against that   I suggest not going fancy on the regex  hard to maintain long-term  so many exceptions   Go for 90  match against the database  do the rest manually   Do get a copy of Postal Addressing Standards  USPS  at http   pe usps gov cpim ftp pubs Pub28 pub28 pdf and notice it is 130  pages long  Regexes to implement that would be nuts   For international addresses  all bets are off  US-based workers would not be able to validate    Alternatively  use a data service  I have  however  no recommendations   Furthermore  when you do send out the stuff in the mail  that s what it s for  right   make sure you put  address correction requested  on the envelope  in the right place  and update the database   We made a simple gui for the front desk person to do that  the person who actually sorts through the mail   Finally  when you have scrubbed data  look for duplicates

User · Answer

I ve been working in the address processing domain for about 5 years now  and there really is no silver bullet  The correct solution is going to depend on the value of the data  If it s not very valuable  throw it through a parser as the other answers suggest  If it s even somewhat valuable you ll definitely need to have a human evaluate correct all the results of the parser  If you re looking for a fully automated  repeatable solution  you probably want to talk to a address correction vendor like Group1 or Trillium

User · Answer

I don t know HOW FEASIBLE this would be  but I haven t seen this mentioned so I thought I would go ahead and suggest this   If you are strictly in the US    get a huge database of all zip codes  states  cities and streets  Now look for these in your addresses  You can validate what you find by testing if  say  the city you found exists in the state you found  or by checking if the street you found exists in the city you found  If not  chances are John isn t for John s street  but is the name of the addressee    Basically  get the most information you can and check your addresses against it  An extreme example would be to get A LIST OF ALL THE ADDRESSES IN THE US OF A and then find which one has the most relevant match to each of your addresses

User · Answer

SmartyStreets has a new feature that extracts addresses from arbitrary input strings   Note  I don t work at SmartyStreets   It successfully extracted all addresses from the sample input given in the question above   By the way  only 9 of those 10 addresses are valid   Here s some of the output  And here s the CSV-formatted output of that same request  ID Start End Segment Verified Candidate Firm FirstLine SecondLine LastLine City State ZIPCode County DpvFootnotes DeliveryPointBarcode Active Vacant CMRA MatchCode Latitude Longitude Precision RDI RecordType BuildingDefaultIndicator CongressionalDistrict Footnotes 1 32 79  quot 2299 Lewes-Georgetown Hwy  Georgetown  DE 19947 quot  N                       2 81 119  quot 11522 Shawnee Road  Greenwood DE 19950 quot  Y 0  11522 Shawnee Rd  Greenwood DE 19950-5209 Greenwood DE 19950 Sussex AABB 199505209226 Y N N Y 38 82865 -75 54907 Zip9 Residential S  AL N  3 121 160  quot 144 Kings Highway  S W  Dover  DE 19901 quot  Y 0  144 Kings Hwy  Dover DE 19901-7308 Dover DE 19901 Kent AABB 199017308444 Y N N Y 39 16081 -75 52377 Zip9 Commercial S  AL L  4 190 232  quot 2 Penns Way Suite 405 New Castle  DE 19720 quot  Y 0  2 Penns Way Ste 405  New Castle DE 19720-2407 New Castle DE 19720 New Castle AABB 197202407053 Y N N Y 39 68332 -75 61043 Zip9 Commercial H  AL N  5 247 285  quot 33 Bridle Ridge Court  Lewes  DE 19958 quot  Y 0  33 Bridle Ridge Cir  Lewes DE 19958-8961 Lewes DE 19958 Sussex AABB 199588961338 Y N N Y 38 72749 -75 17055 Zip7 Residential S  AL L  6 306 339  quot 2742 Pulaski Hwy Newark  DE 19711 quot  Y 0  2742 Pulaski Hwy  Newark DE 19702-3911 Newark DE 19702 New Castle AABB 197023911421 Y N N Y 39 60328 -75 75869 Zip9 Commercial S  AL A  7 341 378  quot 2284 Bryn Zion Road  Smyrna  DE 19904 quot  Y 0  2284 Bryn Zion Rd  Smyrna DE 19977-3895 Smyrna DE 19977 Kent AABB 199773895840 Y N N Y 39 23937 -75 64065 Zip7 Residential S  AL A N  8 406 450  quot 1500 Serpentine Road  Suite 100 Baltimore MD quot  Y 0  1500 Serpentine Rd Ste 100  Baltimore MD 21209-2034 Baltimore MD 21209 Baltimore AABB 212092034250 Y N N Y 39 38194 -76 65856 Zip9 Commercial H  03 N  9 455 495  quot 580 North Dupont Highway Dover  DE 19901 quot  Y 0  580 N DuPont Hwy  Dover DE 19901-3961 Dover DE 19901 Kent AABB 199013961803 Y N N Y 39 17576 -75 5241 Zip9 Commercial S  AL N  10 497 525  quot P O  Box 778 Dover  DE 19903 quot  Y 0  PO Box 778  Dover DE 19903-0778 Dover DE 19903 Kent AABB 199030778781 Y N N Y 39 20946 -75 57012 Zip5 Residential P  AL   I was the developer who originally wrote the service  The algorithm we implemented is a bit different from any specific answers here  but each extracted address is verified against the address lookup API  so you can be sure if it s valid or not  Each verified result is guaranteed  but we know the other results won t be perfect because  as has been made abundantly clear in this thread  addresses are unpredictable  even for humans sometimes

User · Answer

Try www address-parser com  We use their web service  which you can test online

[string] Parse usable Street Address, City, State, Zip from a string

Examples related to string

Examples related to parsing

Examples related to sql-server-2005

Examples related to street-address