Changes

Z:/PatentAddress

In no particular order:

**Iterate!

As mentioned, the ptoassigneend_us_extracted is cleaned. This section works on the remaining records which are stored in ptoassigneend_us_temp.

First, filter out records with city that is a city, zip that is a zip, state that is a state. Note: The consistency between city and state or city and postcode is not checked in this section.

Match the pattern 5-4 or 5 digits.

Select distinct state records with

The output shows that all the records not null or not spaces are valid state names.

Select distinct city records in ptoassigneend_us_extracted and store them in ptoasigneend_us_citylist (775).

-- SELECT 2574811 (992450 LEFT)

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.