329
edits
Changes
Jump to navigation
Jump to search
→ptoassigneend_us_cleaned.postcode_*_cleaned is total garbage
23219 | | |
73114 | | |
(5 rows)</nowiki>
Moreover, selecting from the tables that <code>ptoassigneend_us_cleaned</code> is derived from did not yield this string. Therefore, there is likely an error in the SQL script, perhaps with some wonky copy-pasting from the internet.
==== ...easy gains can be gotten as a quick fix ====
<nowiki>
patent=# select count(*) from ptoassigneend_us_cleaned where postcode_cleaned = E'£\u009B\u0084Ê\u0082Ò£\u009B\u0084Ë\u0082' and postcode is not null;
count
---------
3371136
(1 row)
patent=# select count(*) from ptoassigneend_us_cleaned where postcode_cleaned = E'£\u009B\u0084Ê\u0082Ò£\u009B\u0084Ë\u0082' and char_length(postcode) = 5;
count
---------
2971542
(1 row)</nowiki>
==== ...5 digit postcode works great ====
<nowiki>
patent=# select postcode from ptoassigneend_us_cleaned where postcode_cleaned = E'£\u009B\u0084Ê\u0082Ò£\u009B\u0084Ë\u0082' and char_length(postcode) = 5 limit 12;
postcode
----------
75024
55379
94538
23219
73114
95134
33487
60603
84604
20191
97213
10504
(12 rows)</nowiki>
==== ...9 digit postcode works ok (two forms) ====
<nowiki>
patent=# select postcode from ptoassigneend_us_cleaned where postcode_cleaned = E'£\u009B\u0084Ê\u0082Ò£\u009B\u0084Ë\u0082' and char_length(postcode) = 9 limit 12;
postcode
-----------
USA 20585
USA 33458
4826-2766
181951501
772522463
USA 27601
772522463
USA 20310
USA 47202
174012991
913929221
USA 12345
(12 rows)</nowiki>
==== ...10 digit postcode works great ====
<nowiki>
patent=# select postcode from ptoassigneend_us_cleaned where postcode_cleaned = E'£\u009B\u0084Ê\u0082Ò£\u009B\u0084Ë\u0082' and char_length(postcode) = 10 limit 12;
postcode
------------
92121-1714
95134-1706
80527-2400
95066-4544
01862-2000
07962-2245
90245-5012
55133-3427
30332-0415
95134-1706
20892-7660
94080-4990
(12 rows)</nowiki>
== The Ugly ==
