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.