Databases
StackOverflow2013
Postsdbo
POParse usable Street Address, City, State, Zip from a string
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POParse usable Street Address, City, State, Zip from a string
    primarykey
    data
    text
    <p>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 individual sections of the address into their appropriate fields in a normalized table. I need to do this for approximately 4,000 records and it needs to be repeatable.</p> <p>Assumptions:</p> <ol> <li><p>Assume an address in the US (for now)</p></li> <li><p>assume that the input string will sometimes contain an addressee (the person being addressed) and/or a second street address (i.e. Suite B)</p></li> <li><p>states may be abbreviated</p></li> <li><p>zip code could be standard 5 digit or zip+4</p></li> <li><p>there are typos in some instances</p></li> </ol> <p>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)</p> <p>Sample Data:</p> <ul> <li><p>A. P. Croll &amp; Son 2299 Lewes-Georgetown Hwy, Georgetown, DE 19947</p></li> <li><p>11522 Shawnee Road, Greenwood DE 19950</p></li> <li><p>144 Kings Highway, S.W. Dover, DE 19901</p></li> <li><p>Intergrated Const. Services 2 Penns Way Suite 405 New Castle, DE 19720</p></li> <li><p>Humes Realty 33 Bridle Ridge Court, Lewes, DE 19958</p></li> <li><p>Nichols Excavation 2742 Pulaski Hwy Newark, DE 19711</p></li> <li><p>2284 Bryn Zion Road, Smyrna, DE 19904</p></li> <li><p>VEI Dover Crossroads, LLC 1500 Serpentine Road, Suite 100 Baltimore MD 21</p></li> <li><p>580 North Dupont Highway Dover, DE 19901</p></li> <li><p>P.O. Box 778 Dover, DE 19903</p></li> </ul>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USMatt
    1. USRob Allen
    plurals
    1. PL
      singulars
      1. LTLinked
    1. PL
      singulars
      1. LTLinked
    2. PL
      singulars
      1. LTLinked
    3. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POParse usable Street Address, City, State, Zip from a string
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POParse usable Street Address, City, State, Zip from a string
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POParse usable Street Address, City, State, Zip from a string
      1. This table or related slice is empty.
      1. VTUpMod
    1. COThanks for the sample data!
      singulars
      1. POParse usable Street Address, City, State, Zip from a string
      1. USShaun Bowe
    2. COGood question and very interesting answers. Work backwards from zip seems to be a common theme, but if you're taking raw data from customers the zip may not be accurate. I'd guess that most sites have a disproportionate number of addresses in 90210 for example.
      singulars
      1. POParse usable Street Address, City, State, Zip from a string
      1. USKevin Williams
    3. CO@Kevin: Yes, because you Americans like to lock us Canadians out by requiring a "zip" code, and not accepting our postal codes, thus forcing us to enter some jibberish to bypass the system.... unfortunately, the only zip I know is 90210 :-) Edit: Nevermind... you apparently live a few kilometers away from me in BC. You probably do the same thing too then :-P
      singulars
      1. POParse usable Street Address, City, State, Zip from a string
      1. USmpen
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload