353
edits
Changes
Jump to navigation
Jump to search
Patent Assignment Data Restructure (view source)
Revision as of 17:27, 10 March 2017
, 17:27, 10 March 2017no edit summary
SELECT COUNT(*) FROM ptoproperty WHERE documentid LIKE 'D%';
--1128247
===Restructure Address Information===
The addrline1 and addrline2 columns include post code, city and state information while the state, post code and country columns may have missing values. Besides, some city records also include post code and country information.
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.
The SQL code is as follows:
UPDATE ptoassigneend_missus
SET postcode_city= SUBSTRING(city, '\d{5}[-]\d{4}')
WHERE city ~* '.*\d{5}[-]\d{4}.*';
