Drill 5 of 10 in Database Analyst
0

Database Analyst · TRIM · NULLIF · regexp_replace · ::numeric · ::date

5. Parse a text-only import

Concept: TRIM · NULLIF · regexp_replace · ::numeric · ::dateshow

Spreadsheet imports often arrive as all-text columns. To turn text into a number or date: strip the formatting (regexp_replace(x, '[$,]', '', 'g')), turn placeholders like '' or 'N/A' into NULL with NULLIF, then cast with ::numeric or ::date. Casting '' or ' ' fails, so make them NULL first.

Example (different data):

SELECT TRIM(city) AS city,
  NULLIF(regexp_replace(pop_text, '[, ]', '', 'g'), '')::int AS population
FROM census_raw;

Your task

Accounts payable loaded a vendor spreadsheet where every column is text, and the amounts can't be summed until they're real numbers.

Return id, vendor (with leading and trailing spaces trimmed), amount (amount_text as numeric, with $, commas and spaces removed; 'N/A', empty or spaces-only becomes NULL) and invoice_date (invoice_date_text as a date; empty or spaces-only becomes NULL) for every row of invoice_import_raw. Sort by id.

Starting Postgres in your browser…

Checking your access…