We often want to record telephone numbers of the people represented in our computer systems. These numbers might enter the system by being dictated to the operator of the computer system, or the owner of the telephone number may enter it in a web-form, or it might be written on a paper form and then copied into the system. Mistakes often creep in, and people enter their phone numbers in different formats, with spaces, hyphens, parentheses and other punctuation. Sometimes people include country codes; mostly they do not. For UK landlines, people usually include their area code; sometimes they do not.
In a typical system it is common for around 2% of the numbers to be wrong - If you call one of these numbers you may reach the wrong person or nobody at all.
This page is about validating telephone numbers, specifically numbers used in the United Kingdon of Great Britain and Northern Ireland. There is also some validation of international numbers, but this is limited to the international dialing code.
Telephone numbers are surprisingly complicated. The ranges of numbers have grown enormously over the years, and the changes that were made to increase capacity have generally tried to limit short-term impact, often at the expense of consistency. Different countries have followed different strategies and the level of change and diversity has caused increasing complexity.
What I describe on this page is a comparatively simple validation approach that will find most types of errors that occur in UK telephone numbers. It also returns the telephone number in a standardised format for human beings to read, as well as a digits-only version which is best for automated dialing.
You will need a PostgreSQL database to use this method, although it would be quite easy to port it to other databases and other languages.
Download link: phone_number_check (Windows)
Download link: phone_number_check (Mac/Linux)
On Mac and Windows: double-click on the zip file to unzip it.
On Linux: Depending on your Linux version, you may be able to double-click on the zip file to unzip it. Otherwise use the command:
unzip phone_number_check_mac_linux.zip
The phone_number_check procedure uses two reference tables: one for UK area codes and one for international dialing codes. The scripts to create these are:
create_table_uk_area_code.sqlcreate_table_international_code.sql
By default these scripts create the tables in the public schema. If you want to put them in another schema, (for example: "data_quality"), change the first line of each script to add the schema, like this:
create table data_quality.uk_area_code
The easiest way to load the data is by using the \copy command in the plsql command line. The commands are in the file postgresql_copy_commands.sql. You need to edit the path to point to wherever you downloaded the csv files.
If you want to open the .csv files, use Notepad++ or Notepad. Do not open these files with Excel - if you do, they will never be the same again.
As an alternatively to the psql \copy command, you can use a tool such as DBeaver.
create_table phone_number_check.sql
As with the other tables, you can add a schema name to put it somewhere other than the public schema.
"digits_only"
Run the script create_function_digits_only.sql
"phone_number_check"
Run the script create_procedure_phone_number_check.sql
Having done the set-up in the previous 5 steps, you can run the procedure as often as you like. You will probably want to truncate the results table ("phone_number_check") before every run.
We have found this procedure to take about 1 minute for every 180,000 phone numbers. If you have a million phone numbers, expect it to take about 6 minutes.
To run the procedure, use psql, DBeaver, or your chosen PostgreSQL client to enter the following command, changing the parameters to match your telephone number data:
call phone_number_check('target_schema','source_schema','table_name','phone_column_name','key_column_name');
target_schema is the name of the schema that holds the phone_number_check result table.
source_schema is the name of the schema that holds the table that contains the phone numbers that you want to check.
table_name is the name of the table that contains the phone numbers that you want to check.
phone_column_name is the name of the column that contains the phone numbers that you want to check.
key_column_name is the name of the column that contains the record key, so that you can find the record coantaining a bad phone number. The key column can be an integer, or a character string, or even a UUID (known by Microsoft only, as a GUID).
Having run this procedure, you can look at the phone_number_check table to see what happened. The comment column is blank (null) if the phone number has a correct format. Otherwise the comment tells you what is wrong with the phone number, such as "UK mobile number too short".
For correctly-composed phone numbers, the phone_number_check table tells you:
Ofcom is the official source for "area codes" used in the UK. This page has geographical codes, and, at the end of the page, has links to the other prefixes and short codes (such as 999 for emergencies). Each area code shows the name of the area that it covers. We create a table of these in the database as a reference for validation. At the time of writing there are 774 area codes. It is important to check the Ofcom list from time to time because codes are changed and added.
For international dialing codes, the International Telecommunications Union is the definitive reference. You may find that it is not updated as frequently as we might like.
This one is guidance for those building government systems that need to capture telephone numbers. It is widely applicable.
Google’s libphonenumber is a Java library for comprehensive validation across all countries.