Changes

Jump to navigation Jump to search
====Clean city & state====
 
*Pattern 1: 'city' contains punctuation
 
Remove punctuation marks. 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
*Pattern 2: 'city' is like 'city name, state ID'

Navigation menu