Re:How do you store numbers with leading zeros?\
by geophile on Tuesday September 21, 2021 @09:23AM
(#28604781)
The question conflates representation and presentation.
To be concrete, let's suppose we're talking about 5-digit, zero-padded numbers. Those are integers in the range [0, 99999]. Any database system will allow you to model this value and enforce the constraint in the DDL.
Storage is compact, the constraint is system-enforced, and querying works correctly, i.e., you get the right result whether your query bounds are zero-padded or not (with some assumptions about permitted literal syntax).
The zero-padding is only relevant for presentation, which should not be mixed up with the modeling in any case.
Also, this assumes fixed-length. If, for example, 5, 05, 005, 0005 and 00005 are all valid and different, then just use a string.
To be concrete, let's suppose we're talking about 5-digit, zero-padded numbers. Those are integers in the range [0, 99999]. Any database system will allow you to model this value and enforce the constraint in the DDL.
Storage is compact, the constraint is system-enforced, and querying works correctly, i.e., you get the right result whether your query bounds are zero-padded or not (with some assumptions about permitted literal syntax).
The zero-padding is only relevant for presentation, which should not be mixed up with the modeling in any case.
Also, this assumes fixed-length. If, for example, 5, 05, 005, 0005 and 00005 are all valid and different, then just use a string.