353
edits
Changes
Jump to navigation
Jump to search
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 ~* '[,]\s{1,}\w{2}' OR city ~* '[,]\s{1,}\w{1}[.]\w{1}[.]'
;
#SELECT 1254
Extract city and state info with SQL code:
SELECTseSEL
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)
-- SELECT 51050
Replace(city, ',','')
The SQL function is in
SELECT 847
* city, state postcode(5)
'.*[,].*\d{5}'
* city, state code
'.*[,]\s{0,}\w{2}$'
-- 284
* city, state code
* IS NOT NULL AND city != The details and SQL function are in E:\'McNair\' AND city !~* '([,]|[Projects\PatentAddress\Cleang_Step2.])'sql
Problem: canThe output is table ptoassigneend_us_postex2 which include a new feature 't identify citiescity_extracted'.
-- 23501 CREATE TABLE ptoassigneend_us_postex2 AS SELECT *, ExtractCity2(city) city_extracted FROM ptoassigneend_us_postex;
Patent Assignment Data Restructure (view source)
Revision as of 16:30, 1 May 2017
, 16:30, 1 May 2017→Clean city
*Pattern 1: 'city' is like 'city name, state ID'
*Pattern 2: 'city' is like 'city name, state postcode (5 digits)'
*Pattern 3: 'city' is like 'city name,'
The SQL function is: CREATE OR REPLACE FUNCTION ExtractCity2(adr text) RETURNS text AS $$ SELECT CASE WHEN adr ~* '.*[,]\s{01,}\w{2}(\s{1,}|$|[.]|[,])' OR adr ~* '[,]\s{1}\w{1}[.]\w{1}[.]' THEN REPLACE(SUBSTRING(adr, '.*[,]'),',','') WHEN adr ~* '[,].*\d{5}$' THEN REPLACE(SUBSTRING(adr, '.*[,]'),',','') WHEN adr ~* '.*[,]$' THEN REPLACE(adr, ',', '') ELSE adr END AS result; $$ LANGUAGE SQL;
