353
edits
Changes
Jump to navigation
Jump to search
Patent Assignment Data Restructure (view source)
Revision as of 15:36, 20 April 2017
, 15:36, 20 April 2017→Identify Clean Data
SELECT DISTINCT state FROM ptoassigneend_us_temp;
The output shows that all the records not null or not space spaces are valid state names.
=====city that is a city=====
-- SELECT 2574811 (992450 LEFT)
=====Output: ptoassigneend_us_clean1=====
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.
SQL Code:
CREATE TABLE ptoassigneend_us_clean1 AS
SELECT *
FROM ptoassigneend_us_temp
WHERE city IN (
SELECT citylist
FROM ptoassigneend_us_citylist) AND
state IS NOT NULL AND state != '' AND
postcode ~* '\d{5}';
SELECT 2483936
