When to Store Numbers as Text

Introducing the "Numeroliteral Test," a one-question heuristic for deciding whether to store numeric data in a character-based field.

When to Store Numbers as Text

Not all number-based fields should be stored as numeric data types.

Any data that you can store in a numeric field can also be stored in a text field.  So, how do you decide which one to choose?  For example, how would you store the following?

  • 5-digit US ZIP codes
  • 9-digit (5 + 4) US ZIP codes
  • Phone numbers
  • International Standard Book Numbers (ISBN)
  • Universal Product Codes (UPC)
  • Stock Keeping Units (SKU)
  • Check Numbers
  • Bank Account Numbers
  • Bank Routing Numbers
  • Student Grades (0 - 100 scale)
  • Student Grade Point Averages (0 - 4.0 scale)
  • US Social Security Numbers

The Numeroliteral Test

The word "numeroliteral" derives from the Latin numerus ("a number") and the Latin litera ("letter of the alphabet").  

Yes, I made it up.

The Numeroliteral Test is a heuristic for determining whether data should be stored in a numeric database type or a character-based database type.  To apply the test, you need only answer one simple question:

  1. Does it make sense to add, subtract, multiply, or divide the contents of the field?

If the answer is Yes, then store the data in a numeric field.

If the answer is No, then store the data in a character-based field.


External references

Heuristic (computer science) - Wikipedia

Image by Kamalakannan PM from Pixabay

UPDATE [2021-11-26]: Changed article title from "Storing Numbers as Text" to "When to Store Numbers as Text."  URL slug stayed the same to avoid breaking links.

All original code samples by Mike Wolfe are licensed under CC BY 4.0