353
edits
Changes
Jump to navigation
Jump to search
Patent Assignment Data Restructure (view source)
Revision as of 16:30, 27 April 2017
, 16:30, 27 April 2017→Clean Address: more patterns
=== Clean Address: more patterns ===
*Feature Pattern 1: 'city may contain ' contains punctuation.
Remove punctuation marks. Then match city with ptoassigneend_us_citylist.
postcode ~* '\d{5}';
# SELECT 14508
*Pattern 2: 'city' has the pattern 'city name, state ID'
Extract city and state info with SQL code
SELECT REPLACE(SUBSTRING(city, '.*[,]'),',','') city_candid,
REPLACE(SUBSTRING(city, '[,].*'),',','') state_candid
FROM ptoassigneend_us_temp3
WHERE city ~* '[,]\w{2}' OR city ~* '[,]\s{1}\w{1}[.]\w{1}[.]'
;
*
*Feature city is null or spaces (Not Clean)
