Changes

Patent Assignment Data Restructure (view source)

185 bytes added , 14:57, 29 March 2017

For example,

city | postcode_city

NEW YORK, NY 10022-3201 | 10022-3201

The extracted post code records are stored in table ptoassigneend_missus_final.

::SQL code is in:

E:/McNair/Projects/PatentAddress/RxPostcode.sql

:'''3. State (U.S.)'''

~~:: There are some~~ Some patterns ~~that~~ can be used to extract state information.

::*'[,] State Postcode'

~~:::~~The state and post code are always together, separated by a space. We can extract state information with regular expression

'([,]|[.])\s\w{2,}\s{0,}\w{0,}\s{1,}\d{5}[-]\d{4}'

SQL Code:

WHEN city ~* '([,]|[.])\s\w{2,}\s{0,}\w{0,}\s{1,}\d{5}[-]\d{4}'

THEN LTRIM(RTRIM(replace(regexp_replace(SUBSTRING(city, '[,]\s\w{2,}\s{0,}\w{0,}\s{1,}\d{5}[-]\d{4}'), '\d{5}-\d{4}', ''),',','')))

:::For example,

HOUSTON, TEXAS 77256-6571 | TEXAS

BROOKINGS, SOUTH DAKOTA 57006-0128 | SOUTH DAKOTA

::*'\s State(abbreviation) Postcode'

353

edits