353
edits
Changes
Jump to navigation
Jump to search
*a. '[,] State Postcode'
*b. '\s State(abbreviation) Postcode'
*c. 'D.C.'
*a. '\s{2,} CityName [,] State Postcode' SQL code:
:::''Some noise exists (just a little).''
::*b. '[,]\s{1,} CityName [,] State Postcode'
::* c. 'CityName [,] State Postcode' (no leading spaces)
::* d. 'CityName State(abbreviation) Postcode' (no leading spaces)
::* e. 'CityName State (full name) Postcode' (no leading spaces)
:::''This pattern can't be identified because of much noise.''
::* f. 'CityName Postcode' (no leading spaces)
:::''This pattern can't be identified because of the noise:''
::*Other Noise:
::SQL code is in:
:'''5. Output (Tables)'''::*ptoassigneend_allus
Patent Assignment Data Restructure (view source)
Revision as of 15:03, 29 March 2017
, 15:03, 29 March 2017→Restructure Address Information
Some patterns can be used to extract state information.
The state and post code are always together, separated by a space. We can extract state information with regular expression
BROOKINGS, SOUTH DAKOTA 57006-0128 | SOUTH DAKOTA
'(^|\s)\w{2}\s{1}\d{5}[-]\d{4}'
WAUKEGAN IL 60085-2195 |IL
'D[.]C[.]\s\d{5}-\d{4}'
Z:/PatentAddress/
CASE WHEN addrline1 ~* '\s{2,}\w{1,}\s{0,}\w{0,}\s{0,}[,]\s{0,}\w{1,}\s{0,}\w{0,}\s{0,}\d{5}[-]\d{4}'
THEN LTRIM(RTRIM(REPLACE(SUBSTRING(SUBSTRING(addrline1, '\s{2,}\w{1,}\s{0,}\w{0,}\s{0,}[,]\s{0,}\w{1,}\s{0,}\w{0,}\s{0,}\d{5} [-]\d{4}'),'.*[,]'),',','')))
Examples:
800 CHRYSLER DR. EAST AUBURN HILLS, MICHIGAN 48326-2757 |AUBURN HILLS
550 MADISON AVENUE NEW YORK, NEW YORK 10022-3201 |NEW YORK
P.O. BOX 15439 WILMINGTON, DE 19850-5439 |WILMINGTON
1313 N. MARKET STREET HERCULES PLAZAWILMINGTON, DE 19894-0001
SQL code:
CASE WHEN addrline1 ~* '\s{2,}\w{1,}\s{0,}\w{0,}\s{0,}[,]\s{0,}\w{1,}\s{0,}\w{0,}\s{0,}\d{5}[-]\d{4}'
THEN LTRIM(RTRIM(REPLACE(SUBSTRING(SUBSTRING(addrline1, '\s{2,}\w{1,}\s{0,}\w{0,}\s{0,}[,]\s{0,}\w{1,}\s{0,}\w{0,}\s{0,}\d{5}[-]\d{4}'),'.*[,]'),',','')))
Example:
920 DISC DRIVE, SCOTTS VALLEY, CA 95067-0360 |SCOTTS VALLEY
550 MADISON AVENUE, NEW YORK, NY 10022-3201 |NEW YORK
BALLSTON TOWER ONE 800 NORTH QUINCY STREET, ARLINGTON, VA 22217-5660 |ARLINGTON
SQL code:
WHEN addrline1 ~* '^\w{1,}\s{0,}\w{0,}\s{0,}[,]\s{0,}\w{1,}\s{0,}\w{0,}\s{0,}\d{5}[-]\d{4}'
THEN REPLACE(SUBSTRING(SUBSTRING(addrline1, '^\w{1,}\s{0,}\w{0,}\s{0,}[,]\s{0,}\w{1,}\s{0,}\w{0,}\s{0,}\d{5}[-]\d{4}'), '.*[,]'),',', '')
Examples:
PHILADELPHIA, PA 19104-3147 |PHILADELPHIA
ROCHESTER, NY 14650-2201 |ROCHESTER
SQL code:
WHEN addrline1 ~* '^\w{1,}\s{0,}\w{0,}\s{0,}\w{2}\s{0,}\d{5}[-]\d{4}'
THEN regexp_replace(SUBSTRING(addrline1, '^\w{1,}\s{0,1}\w{0,}\s{1,}\w{2}\s{0,}\d{5}[-]\d{4}'), '\w{2}\s\d{5}[-]\d{4}', '')
Examples:
TARRYTOWN NY 10591-6706 |TARRYTOWN
NEW YORK NEW YORK 10022-3201
LITTLE ELM 75068-3787
OAK RIDGE 37831-6498
MASSACHUSETTS 02780-7319 ('State Postcode')
BATON, ROUGE, LA 70809-4562 (the city name is separated by a comma)
ST. PAUL, MIN 55133-3427 (city name contains a dot)
BOX 87703CHICAGO, IL 60680-0703 (no space between street and city name :(
E:/McNair/Projects/PatentAddress/RxCity.sql
:::This table contains all the U.S. patents extracted from ptoassigneend table. The rule to generate this table is 'country = 'UNITED STATES' ' or any of the features contains U.S. postcode.
