Some SQL for the Day

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:

1) The parcels in the dump did not have dashes in the APN Number.

2) The address number was padded with leading zeros.

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.

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:

UPDATE parcel
SET apn_number = REPLACE (apn_number, ‘-‘, ”);

With the REPLACE function I’m telling Oracle to do the following:

• Look at my apn_number column
• Find all of the dashes in the column
• Replace the dashes with  "nothing" or two single quotes (”)

Fairly straightforward and very fast (and useful).

2) To eliminate the leading zeros I used the TRIM function. Here’s the update syntax:

UPDATE parcel
SET street_number = TRIM(LEADING 0 FROM my_street_number)

With the TRIM function I’m telling Oracle to do the following:

• Look at the start (lead) of the my_street_number column
• Find all of the zeroes (0s) at the start of the column
• Remove all of the leading zeroes until you get to a non-zero number, then stop the process

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.