Is there a standard for storing normalized phone numbers in a database?

by

First, beyond the country code, there is no real standard. About the best you can do is recognize, by the country code, which nation a particular phone number belongs to and deal with the rest of the number according to that nation’s format.

Generally, however, phone equipment and such is standardized so you can almost always break a given phone number into the following components

  • C Country code 1-10 digits (right now 4 or less, but that may change)
  • A Area code (Province/state/region) code 0-10 digits (may actually want a region field and an area field separately, rather than one area code)
  • E Exchange (prefix, or switch) code 0-10 digits
  • L Line number 1-10 digits

With this method you can potentially separate numbers such that you can find, for instance, people that might be close to each other because they have the same country, area, and exchange codes. With cell phones that is no longer something you can count on though.

Further, inside each country there are differing standards. You can always depend on a (AAA) EEE-LLLL in the US, but in another country you may have exchanges in the cities (AAA) EE-LLL, and simply line numbers in the rural areas (AAA) LLLL. You will have to start at the top in a tree of some form, and format them as you have information. For example, country code 0 has a known format for the rest of the number, but for country code 5432 you might need to examine the area code before you understand the rest of the number.

You may also want to handle vanity numbers such as (800) Lucky-Guy, which requires recognizing that, if it’s a US number, there’s one too many digits (and you may need to full representation for advertising or other purposes) and that in the US the letters map to the numbers differently than in Germany.

You may also want to store the entire number separately as a text field (with internationalization) so you can go back later and re-parse numbers as things change, or as a backup in case someone submits a bad method to parse a particular country’s format and loses information.

More Related Contents:

  1. Display dynamic image from database or remote source with p:graphicImage and StreamedContent
  2. Version of SQLite used in Android?
  3. What are the performance characteristics of sqlite with very large database files? [closed]
  4. Primary keys with Apache Spark
  5. What are database normal forms and can you give examples? [closed]
  6. Exposing database IDs – security risk?
  7. How can I put a database under git (version control)?
  8. How to use mongoimport to import csv
  9. NoSql vs Relational database
  10. How do IMMUTABLE, STABLE and VOLATILE keywords effect behaviour of function?
  11. Best way to test a MS Access application?
  12. Partial Dependency (Databases)
  13. Can I rollback a transaction I’ve already committed? (data loss)
  14. How to backup sqlite database?
  15. Microservices with shared database? using multiple ORM’s? [closed]
  16. What’s the best strategy for unit-testing database-driven applications?
  17. data block size in HDFS, why 64MB?
  18. How should international geographical addresses be stored in a relational database?
  19. how to use Blob datatype in Postgres
  20. Is there any option for local database like Sqlite for j2me – CLDC devices?
  21. Are database triggers necessary? [closed]
  22. Which is a good design for a database table that can be owned by two different resources, and therefore needs two different foreign keys? [closed]
  23. What is the difference between Non-Repeatable Read and Phantom Read?
  24. Ideas on database design for capturing audit trails [closed]
  25. I need a client side browser database. What are my options [closed]
  26. Android: Where are database files stored?
  27. How to get item count from DynamoDB?
  28. How to find SQLITE database file version
  29. Oracle: How do I convert hex to decimal in Oracle SQL?
  30. Pros/cons of document-based databases vs. relational databases

Leave a Comment