353
edits
Changes
Jump to navigation
Jump to search
Patent Assignment Data Restructure (view source)
Revision as of 16:38, 22 March 2017
, 16:38, 22 March 2017→Restructure Address Information
::*The object of this project is to extract city, country and postcode information from the three features above.
::*By now, we only focus on cleaning American patents.
:'''2. Tables'''
::*ptoassigneend_allus
:::This table contains all the U.S. patents extracted from ptoassigneend table. The rule to extract this table is either by 'country = 'UNITED STATES'' or postcode ~* '\d{5}-\d{4}'.
:::The SQL code is:
CREATE TABLE ptoassigneend_allus AS
SELECT * FROM ptoassigneend
WHERE city ~* '.*\d{5}[-]\d{4}.*' OR addrline1 ~* '.*\d{5}[-]\d{4}.*' OR
addrline2 ~* '.*\d{5}[-]\d{4}.*' OR postcode ~* '.*\d{5}[-]\d{4}.*' OR
country ~* 'UNITED STATES';
SELECT 3572682
:::The ptoassigneend_allus table may miss some U.S. patents.
:'''2. Postcode(U.S.)'''
