Pad Invoice Numbers — Solution & ExplanationProblems / Pad Invoice Numbers / EditorialApproach

Cast, then pad. LPAD is a string function; feed it CAST(invoice_id AS VARCHAR) and pad to width 5 with '0'.

Concatenate the parts. 'INV-' || year || '-' || padded in SQL; F.concat(F.lit(...), ..., F.lpad(...)) in PySpark.

Key InsightZero-padding exists because string order and numeric order disagree: '7' > '10021' lexicographically. Fixed-width encoding makes the two orders coincide, which is why invoice numbers, log line IDs, and file names use it. The practical detail worth remembering: padding functions are string functions — the cast isn't decoration, and engines differ in whether they'd do it implicitly (and how they'd render the number if they did), so make it explicit.Hints

LPAD(string, 5, '0') left-fills to exactly width 5 — that's the whole zero-padding trick. It needs a STRING input, so cast the integer first.

Build the reference with concatenation: 'INV-' || year || '-' || padded_id. In PySpark that's F.concat with F.lit for the fixed pieces.

Why pad at all? '7' sorts after '10021' as a string; '00007' sorts before it. Fixed-width identifiers make lexicographic order match numeric order.

Solve Pad Invoice Numbers yourself →