Changes

Patent Assignment Data Restructure (view source)

170 bytes added , 15:00, 29 March 2017

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,

city | state_city

NEW YORK, NY 10022-3201 | NY

BROOKINGS, SOUTH DAKOTA 57006-0128 | SOUTH DAKOTA

*'\s State(abbreviation) Postcode'

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

SQL code:~~:*'\s State(abbreviation) Postcode'~~

WHEN city ~* '(^|\s)\w{2}\s{1}\d{5}[-]\d{4}' THEN regexp_replace(SUBSTRING(city, '\w{2}\s{1,}\d{5}[-]\d{4}'), '\d{5}-\d{4}', '')

~~:::~~For example:

NEW YORK NY 10022-3201 |NY

WAUKEGAN IL 60085-2195 |IL

::*'D.C.'

'D[.]C[.]\s\d{5}-\d{4}'

SQL code:~~:The extracted state records are stored in the table ptoassigneend_missus_final.~~

:: WHEN city ~* 'D[.]C[.]\s\d{5}-\d{4}' THEN 'D.C.' The extracted state records are stored in the table ptoassigneend_missus_final. SQL code is in:

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

:'''4. City (U.S.)'''~~:: There are some~~ Some patterns ~~that~~ can be used to extract city information.

::Three lists of samples extracted from addrline1, addrline2 and city are used to summarize the patterns. They are in

Z:/PatentAddress/

::*'\s{2,} CityName [,] State Postcode'

CASE WHEN addrline1 ~* '\s{2,}\w{1,}\s{0,}\w{0,}\s{0,}[,]\s{0,}\w{1,}\s{0,}\w{0,}\s{0,}\d{5}[-]\d{4}'

353

edits