353
edits
Changes
Jump to navigation
Jump to search
Patent Assignment Data Restructure (view source)
Revision as of 17:32, 10 March 2017
, 17:32, 10 March 2017no edit summary
The basic idea to extract information from addrline1 and addrline2 is to search for post code following a specific pattern using regular expression. The state information is always ahead of post code.
U.S. postcode is like [five digits - four digits]. In this way, I created a table named 'ptoassigneend_missus'. Then, using the method above to extract useful address information.
The SQL code is as follows:
SET postcode_city= SUBSTRING(city, '\d{5}[-]\d{4}')
WHERE city ~* '.*\d{5}[-]\d{4}.*';
UPDATE ptoassigneend_missus
SET state_city = SUBSTRING(city, '\w{2,}\s{0,}\d{5}[-]\d{4}')
WHERE city ~* '.*\s{1,}\w{2}\s{0,}\d{5}[-]\d{4}.*';
UPDATE ptoassigneend_missus
SET city_city = SUBSTRING(city, '\w{3,}\s{0,1}\w{0,}\s{0,}[,]')
WHERE city ~* '.*\w{2}\s{1,}\d{5}[-]\d{4}.*';
From addrline1, addrline2 and city, I extracted city, post code and state respectively and stored in 'city_addr1', 'city_addr2', 'city_city'.
