Received a parcel attribute data dump for an outside party today. Needed to clean it up so it matched my internal data. There were a few issues: <\/p>\n
1) The parcels in the dump did not have dashes in the APN Number.<\/p>\n
2) The address number was padded with leading zeros. <\/p>\n
Used some SQL to clean up both data differences. Thought I’d pass it long in case others run into similar problems or have similar requirements. I should note that all of the processing was done inside of the Oracle database via SQL*Plus.<\/p>\n
1) I figured instead of adding dashes to the dump, I write a view and in the view I’d remove the dashes from my data. Made life much easier. Instead of showing the view syntax, I’ll put the code into a simple update statement:<\/p>\n
UPDATE parcel
\nSET apn_number = REPLACE (apn_number, ‘-‘, ”);<\/p>\n
With the REPLACE function I’m telling Oracle to do the following:<\/p>\n
Fairly straightforward and very fast (and useful).<\/p>\n
2) To eliminate the leading zeros I used the TRIM function. Here’s the update syntax:<\/p>\n
UPDATE parcel
\nSET street_number = TRIM(LEADING 0 FROM my_street_number)<\/p>\n
With the TRIM function I’m telling Oracle to do the following:<\/p>\n
In the end, neither SQL statement is complex, but both are very useful. It’s also interesting to note that both statement took under 30 seconds to perform. Very nice considering that there were approximately 250,000 records in both tables. <\/p>\n","protected":false},"excerpt":{"rendered":"
Received a parcel attribute data dump for an outside party today. Needed to clean it up so it matched my…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_mi_skip_tracking":false},"categories":[11],"tags":[],"acf":[],"yoast_head":"\n