Unit 09.03: Splitting and extracting from messy fields

Unit ID: SQL-M09-U04 - Estimated active time: 13-16 minutes Objective: pull structured values out of a text column and validate the extraction.

Splitting on a delimiter

SELECT name,
       SPLIT_PART(name, ' ', 1) AS first_part,
       SPLIT_PART(name, ' ', 2) AS second_part
FROM customers
LIMIT 2;
-- Customer 1 | Customer | 1
-- Customer 2 | Customer | 2

SPLIT_PART(string, delimiter, n) returns the nth piece. Clean here because the format is uniform - which is exactly the condition real data rarely satisfies.

Extraction assumes a shape, so verify the shape first

Before trusting any split, check how many pieces actually exist:

SELECT LEN(name) - LEN(REPLACE(name, ' ', '')) AS spaces,
       COUNT(*)                                AS rows
FROM customers
GROUP BY spaces
ORDER BY rows DESC;
-- 1 | 4812

Every row has exactly one space, so a two-part split is safe. Had this returned a mix of 1, 2, and 0, the naive split would silently produce wrong values for some rows and empty strings for others - with no error.

Why "empty string, not error" is the danger

SPLIT_PART returns '' when the requested piece does not exist. So a query extracting a surname from single-word names returns empty strings that flow into a report as blank cells or, worse, as a category. Always check the shape distribution before extracting.

Useful functions for common shapes

Need	Function
Piece n of a delimited string	`SPLIT_PART(s, delim, n)`
First / last characters	`LEFT(s, n)` / `RIGHT(s, n)`
Section by position	`SUBSTRING(s, start, length)`
Position of a substring	`POSITION(sub IN s)`
Remove surrounding whitespace	`TRIM(s)`
Complex or variable patterns	`REGEXP_EXTRACT(s, pattern)`

Reach for a regex only when the simpler functions genuinely cannot express the shape - a regex is harder to read and harder for a reviewer to check.

Practice

Extract the numeric part of name and confirm every extraction produced a value.

Check your answer

SELECT COUNT(*)                                        AS rows,
       COUNT(*) FILTER (WHERE SPLIT_PART(name,' ',2) = '') AS empty_extractions
FROM customers;
-- 4812 | 0

Zero empty extractions confirms the split worked for every row. Counting the failures is the check - without it you are trusting a shape assumption you never tested.

Takeaway

Verify the shape distribution before extracting, and count the empty results afterwards. Extraction fails quietly, never loudly.

---