353
edits
Changes
Jump to navigation
Jump to search
Patent Assignment Data Restructure (view source)
Revision as of 15:46, 27 April 2017
, 15:46, 27 April 2017→Clean Address: more patterns
# SELECT 14508
*Pattern 2: 'city' has the pattern is like 'city name, state ID'
Extract city and state info with SQL code:
SELECT REPLACE(SUBSTRING(city, '.*[,]'),',','') city_candid,
REPLACE(SUBSTRING(city, '[,].*'),',','') state_candid
;
#SELECT 129
*Pattern 3: 'city' is like 'city name, state postcode (5 digits)'
Extract city and state info with SQL code:
SELECT
REPLACE(SUBSTRING(city, '.*[,]'),',','') city_candid,
REPLACE(REPLACE(SUBSTRING(city, '[,].*\d{5}$'),',',''), '\d{5}', '') state_candid
FROM ptoassigneend_us_temp3
WHERE city ~* '[,].*\d{5}$';
#SELECT 624
*Feature city is null or spaces (Not Clean)
