Shape / columns · All operations

Pad text to a fixed length

A variant of Clean up text

Add characters to the left or right of text until it reaches a given length.

Status

In arcform

Runs as a SQL step

arcform carries it as a sql: step today.

The DuckDB step

Pad text on the left or right to a length

It reads the orders table the proofs share. Create it first:

SQL
CREATE TABLE orders AS SELECT * FROM (VALUES
  (1,  'Alice Ng',      'north', DATE '2026-01-03', 120.00, 'widget', 'a|b',   '{"channel":"web","items":[1,2]}'),
  (2,  'alice ng',      'north', DATE '2026-01-05',  80.50, 'gadget', 'b',     '{"channel":"shop","items":[3]}'),
  (3,  'Bob  Okafor',   'south', DATE '2026-01-05', 300.00, 'widget', 'a|c',   '{"channel":"web","items":[4,5,6]}'),
  (4,  'Carla Díaz',    'east',  DATE '2026-01-09',  NULL,  'gizmo',  NULL,    '{"channel":"web","items":[]}'),
  (5,  'Bob Okafor',    'south', DATE '2026-01-12',  45.25, 'gadget', 'c',     '{"channel":"phone","items":[7]}'),
  (6,  'Dev Patel',     'west',  DATE '2026-01-12', 510.00, 'widget', 'a',     '{"channel":"web","items":[8,9]}'),
  (7,  'Carla Diaz',    'east',  DATE '2026-01-20',  99.99, 'gizmo',  'b|c',   '{"channel":"shop","items":[10]}'),
  (8,  'Dev Patel',     'west',  DATE '2026-02-02', 510.00, 'widget', 'a',     '{"channel":"web","items":[8,9]}'),
  (9,  'Eve Larsen',    NULL,    DATE '2026-02-03',  15.00, 'gadget', NULL,    '{"channel":"web","items":[11]}'),
  (10, 'Alice Ng',      'north', DATE '2026-02-10', 220.00, 'gizmo',  'a|b|c', '{"channel":"phone","items":[12,13]}')
) t(order_id, customer, region, order_date, amount, product, tags, meta);

Then run the statement:

SQL
CREATE TEMP TABLE result AS
SELECT order_id, lpad(order_id::VARCHAR, 5, '0') AS padded_id, rpad(product, 8, '.') AS padded_product FROM orders;
SELECT * FROM result;
SELECT CASE WHEN ((SELECT [padded_id, padded_product] FROM result WHERE order_id = 7) = ['00007', 'gizmo...'] AND (SELECT padded_id FROM result WHERE order_id = 10) = '00010' AND (SELECT count(*) FROM result WHERE length(padded_id) = 5 AND length(padded_product) = 8) = 10) THEN 'as expected' ELSE error('ids were not padded left to 00007 and products right to eight characters') END AS verdict;
  • It needs nothing installed beyond DuckDB itself.
  • The statement checks its own result and raises an error when it is wrong: “ids were not padded left to 00007 and products right to eight characters”.
  • It passed on DuckDB v2.0.0 (Cyanoptera) d54cbbe3df.

What other tools call it

Meridian's classification; an independent blind rating agreed on 95.7% of 324 rows (variant, version 3).

Duckle (1)
  • Pad Stringread 2026-09-25

    left or right pad a string to a fixed length

Ibis (2)
  • LPadread 2026-09-27

    Pad a string on the left to a given length

  • RPadread 2026-09-27

    Pad a string on the right to a given length

Trifacta (1)
  • Pad with leadingread 2026-09-27

    Pad values with leading characters to a common length

Background

In DuckDB, lpad pads a string with a given character on the left until it has the given number of characters, and rpad does the same on the right.[1] A leading zero is any 0 digit that comes before the first nonzero digit in a number string, as in 007, which has two.[2] Leading zeros make ascending numeric order match alphabetical order: 11 sorts before 2 alphabetically, but after 02.[2]

When to use it

Excel automatically removes leading zeros from numerical text such as postal codes, product codes and account numbers, so that formulas can work on them.[3] Padding is how such a code gets its zeros back, provided the code's full width is known and every value should have it. (Meridian's view) Many older computer programs generate leading zeros when creating values for new records and accounts, so utility billing, human resources and government systems are likely to use them.[2]

In DuckDB, lpad and rpad truncate a string that already has more than the target number of characters, cutting it on the right.[1] PostgreSQL's lpad also truncates a string longer than the target length, on the right.[4] Python's ljust and rjust return the original string unchanged when the target width is less than or equal to its length.[5] Check the longest value in the column before padding it. (Meridian's view)

Leading zeros do not make negative numbers sort correctly: −23 comes alphabetically after −01, −1 and −22, though it is less than all of them.[2] Python's zfill inserts the zeros after a leading plus or minus sign rather than before it, so the sign stays first.[5] Pad codes and identifiers, which are text; numbers that will be added up are better left as numbers and given a display format. (Meridian's view)

The method

PostgreSQL's lpad prepends the fill characters, a space by default, so lpad('hi', 5, 'xy') gives xyxhi.[4] In DuckDB, lpad('hello', 8, '>') returns >>>hello, and rpad('hello', 10, '<') returns hello<<<<<.[1] Many digital cameras and other recording devices use leading zeros when saving files, to make names of equal length.[2]

Sources

  1. [1]Text Functions – DuckDBdocumentation
  2. [2]Leading zero - Wikipediasecondary source
  3. [3]Keeping leading zeros and large numbers | Microsoft Supportdocumentation
  4. [4]PostgreSQL: Documentation: 18: 9.4. String Functions and Operatorsdocumentation
  5. [5]Built-in Types — Python 3.15.2 documentationdocumentation