353
edits
Changes
Jump to navigation
Jump to search
====Clean city & state====The details and SQL function are in E:\McNair\Projects\PatentAddress\Cleang_Step2.sql
SELECTSELECTseSEL
Patent Assignment Data Restructure (view source)
Revision as of 17:54, 28 April 2017
, 17:54, 28 April 2017→Clean Address: more patterns
767 FIFTH AVE., NEW YORK, NY 10153 | 10153
Even excluding the PO P.O. BOX # and SUITE #, the false positive rate is still a little bit high.
The output is table ptoassigneend_us_expost which include a new feature 'postcode_extracted'. ====Clean city==== *Pattern 21: 'city' is like 'city name, state ID'
Extract city and state info with SQL code:
Extract city and state info with SQL code:
REPLACE(SUBSTRING(city, '.*[,]'),',','') city_candid,
REPLACE(REPLACE(SUBSTRING(city, '[,].*\d{5}$'),',',''), '\d{5}', '') state_candid
