353
edits
Changes
Jump to navigation
Jump to search
====* Output: ptoassigneend_us_identify1====
Patent Assignment Data Restructure (view source)
Revision as of 15:46, 28 April 2017
, 15:46, 28 April 2017→Identify Clean Data
-- SELECT 2603422
This table stores records that meet all the requirements above: zip with 5-4 or 5 digits, state not null or not spaces, and city in ptoasigneend_us_citylist.
postcode ~* '\d{5}';
SELECT 2511356
* Part of 'city' contains punctuation. Remove punctuation marks, and then match 'city' with ptoassigneend_us_citylist.
CREATE TABLE ptoassigneend_us_temp3 AS
SELECT *, replace(city, ',', '') clean_city
FROM ptoassigneend_us_temp2;
Output: ptoassigneend_us_identify2
CREATE TABLE ptoassigneend_us_identify2 AS
SELECT *
FROM ptoassigneend_us_temp3
WHERE clean_city IN (
SELECT citylist
FROM ptoassigneend_us_citylist) AND
state IS NOT NULL AND state != '' AND
postcode ~* '\d{5}';
# SELECT 14508
=== Clean Address: more patterns ===
