CONCAT and LPAD() Formatting

Concepts covered: String Building

Text leaving the warehouse often has to fit exact widths: invoice numbers with a fixed count of digits, bank files where each field starts at a known column, report lines that align. LPAD(text, size, pad) adds copies of pad on the left until the result is size characters long, and RPAD adds them on the right. With CONCAT joining the pieces, the pair builds any fixed layout.

SELECT
  LPAD('42', 6, '0') AS invoice_seq,
  RPAD('USD', 6, '.') AS currency_col,
  LPAD('7', 6, 'ab') AS cycled_pad,
  LPAD(CAST(42 AS VARCHAR), 6, '0') AS from_integer

Rules behind every result

• size is the final width in characters, so '42' padded to 6 gains 4 zeros.
• A multi-character pad repeats from its start and is cut where the width runs out, which is why cycled_pad reads 'ababa7'.
• The first argument must be VARCHAR. An integer needs CAST(seq AS VARCHAR), as from_integer shows; without it the call fails to resolve.
• An empty pad string or a negative size raises an error.

Truncation keeps the left side

LPAD and RPAD both cut input that is already longer than size down to size characters, keeping the leftmost ones. A name loses its ending. A zero-padded ID loses its last digits, which is worse: 2 different sequence numbers can produce the same invoice number. The animation pads 5 sequence numbers to 6 digits and sorts by the result; watch the last 2 rows.

SELECT
  seq,
  CONCAT('INV-', LPAD(CAST(seq AS VARCHAR), 6, '0')) AS invoice_no
FROM invoices
ORDER BY invoice_no, seq

Sequences 1234567 and 1234568 both became INV-123456. The query finished cleanly, with no error or warning. Size the width for the largest value the column will ever hold, and add a check such as WHERE LENGTH(CAST(seq AS VARCHAR)) > 6 to a data quality job so an overflow is caught before invoices go out.

Padding makes text sort like numbers

Text compares character by character from the left, so '9' sorts after '815': the first characters decide, and the character 9 comes after 8 whatever the lengths. Padding every value to the same width lines the digits up by place, and text order then matches numeric order. That matters wherever IDs are compared as text: file names, object storage keys, joins against a system that keeps IDs as strings.

Signs, decimals and report lines

LPAD treats a minus sign as another character, so LPAD('-42', 6, '0') returns '000-42'. Take the absolute value with ABS and pad its digits, then put the sign in front with CONCAT. For money, CAST to DECIMAL(10, 2) first so every amount shows 2 decimal places, then pad with spaces on the left so the decimal points line up down the column.

SELECT
  CONCAT(
    RPAD(merchant, 12, ' '),
    LPAD(CAST(CAST(amount AS DECIMAL(10, 2)) AS VARCHAR), 10, ' '),
    ' ',
    currency
  ) AS report_line,
  CONCAT(
    CASE WHEN amount < 0 THEN '-' ELSE '+' END,
    LPAD(CAST(CAST(ABS(amount) AS DECIMAL(10, 2)) AS VARCHAR), 9, '0')
  ) AS bank_amount
FROM payouts

In report_line, RPAD cut 'Northwind Coffee' to 'Northwind Co' to hold its 12-character slot, and space padding leaves the refund's minus sign beside its digits. In bank_amount, the CASE writes the sign first and LPAD fills the 9 places after it with zeros.

A refund ledger stores a delta of -7. What does this return?

SELECT
  LPAD(CAST(-7 AS VARCHAR), 4, '0') AS padded

• '00-7': the minus sign counts as 1 of the 4 characters
• '-007': LPAD keeps the minus sign in front of the zeros
• '0-7': the minus sign is left out of the width
• An error, because LPAD rejects negative numbers

Keep formatting in the SELECT list and keep ordering on the data. ORDER BY seq DESC sorts the integers themselves, so it stays right however large the sequence grows. Ordering by the padded text works only while every value fits the width: once 1234567 and 1234568 both read INV-123456, the text alone cannot say which invoice came later.

Each invoice row holds an integer seq. Return seq and an invoice_no made of 'INV-' followed by seq zero-padded to 6 digits, newest invoice first.

About this lesson

This section is part of the String Functions: Advanced lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.