Identifiers that lost their leading zeros

• A spreadsheet guessed that a column of digits was numeric and stored it as a number.
• A database column was defined as an integer when the code only looks like one.
• A file was opened and saved again, and the conversion happened without anyone choosing it.

• Joins to another table fail for exactly the rows whose codes begin with zero.
• Postal codes from some regions become too short to be valid, so mail and tax lookups fail.
• Very long numeric identifiers can also be rounded or shown in scientific notation, which destroys digits that padding cannot bring back.

• Count values by length. A fixed-length code should have one length.
• Check whether the short values are concentrated in one region or one range, which is what dropped zeros look like.
• Look at alignment in a spreadsheet. Codes that sit to the right are being treated as numbers.

• Treat identifiers as text from the moment the file is read, before any software guesses a type.
• Pad back to the full width only when the code has one known, fixed length.
• For codes of varying length, go back to the source. The number of zeros that were dropped is not recoverable from the value.

• Numbers stored as textA column of quantities contains symbols, separators, or words that stop software reading it as numbers.
• Phone number formatsThe same phone number is written with different punctuation, spacing, prefixes, and extensions.
• Rounding and precision lossDigits were dropped when a value was displayed, exported, or converted, and the file now holds the shortened number.
• Case example: Academic researchA survey whose result depended on how blanks were coded
• Case example: UtilitiesEquipment records that could not be tied to what was spent on them
• Case example: E-commerceA catalog where the same product was listed four times

TermsLeading zeros, Identifier, Data type, Type coercion