Do you want to display a readable and consistent identifier, such as ID0042 or CLI-00042 - Dupont? This article presents formula combinations to add a prefix and pad a number with zeros.

Padding a Number with Zeros

The FORMAT_NUMBER function, with a zero mask, pads the number on the left.

The number of zeros in the mask sets the width: "0000" for 4 digits (42 becomes 0042), "00000" for 5 digits.

Adding a Prefix

Combine with CONCAT to attach a prefix before the number.

More complete example, with a separator and the client name:

Variant: Keep Only the Last Digits

To keep only the last n digits of a number, use RIGHT after padding with zeros.

The Case of RowID

ROWID() returns the internal row ID: a large number. FORMAT_NUMBER(ROWID(), "0000") therefore does not add zeros, since the number already has more than four digits. For a short, sequential identifier (ID0001, ID0002, ID0042), use a counter field starting at 1, rather than RowID.