353
edits
Changes
Jump to navigation
Jump to search
Patent Assignment Data Restructure (view source)
Revision as of 17:58, 27 April 2017
, 17:58, 27 April 2017→Clean postcode & state
Postcode should always be at the end of addrline1, addrlin2 or city. Exclude P.O. BOX #.
For example, the SQL code to extract postcode and state from addrline1 is:
SELECT addrline1, SUBSTRING(addrline1, '\d{5}') postcode_cleanpostcode_candid, REPLACE(REPLACE(SUBSTRING(addrline1, '[,]\s{1,}\w{1,}\s{0,}\w{0,}\d{5}') state_clean,'\d{5}',''),',','') state_candid
FROM ptoassigneend_us_temp2
WHERE (addrline1 ~* '\d{5}$' AND NOT (addrline1 ~* 'BOXBO')AND NOT (addrline1 ~* 'P[.]O[.]');
Examples: 181 METRO DRIVE SUITE 520 SAN JOSE, CA 95110 | 95110 | , CA 95110 11065 ROSELLE STREET SAN DIEGO, CALIFORNIA 92121 | 11065 | , CALIFORNIA 92121 400 MARGARET STREET, NO. 20 PLATTSBURGH, NEW YORK 12901 | 12901 |
====Clean city & state====
