Phone number cleansing, purging and formatting using Invantive UniversalSQL functions

Wiki (en)
1

Phone number handling

Going forward from SQL-functions like is_uri and is_iban, Invantive UniversalSQL has been extended by SQL-functions to help Invantive UniversalSQL-users handle phone numbers:

  • is_phone_number: evaluates to boolean true when the value provided is a valid phone number for the country indicated. In case no country is specified, an E.164 notation is required.
  • phone_number_to_e164: evaluates to the phone number in E.164 notation.
  • phone_number_to_national: evaluates to the phone number in national notation.
  • phone_number_to_international: evaluates to the phone number in international notation.
  • phone_number_type: evaluates to the type of phone number from the list below.

The possible types of phone numbers coming from the phone_number_type SQL string function are:

  • FIXED_LINE: fixed landline.
  • FIXED_LINE_OR_MOBILE: either fixed landline phone or mobile number.
  • MOBILE: mobile phone number.
  • PAGER: pager.
  • PERSONAL_NUMBER: personal phone number.
  • PREMIUM_RATE: premium rate.
  • SHARED_COST: shared cost.
  • TOLL_FREE: toll-free
  • UAN: Universal Access Number; a phone number that can be accessed without dialing geographic area codes.
  • UNKNOWN: unknown type.
  • VOICEMAIL: voicemail.
  • VOIP: VoIP.

is_phone_number: is a text a valid phone number?

The SQL-function is_phone_number returns true or false depending on whether the text specified is a valid phone number. At least a text must be specified, but the ISO-country code can be added for non-E164 formatted numbers to specify the country applicable.

Sample with country code:

select is_phone_number('0475691444', 'NL') /* Evaluates to "true". */

Sample without country code:

select is_phone_number('+31475691444') /* Evaluates to "true". */

Sample without country code:

select is_phone_number('+31999691444') /* Evaluates to "false". */

phone_number_to_e164: phone number in E.164 notation

The SQL-function phone_number_to_e164 cleans and rewrites a phone number in the E.164 notation. At least a text must be specified, but the ISO-country code can be added for non-E164 formatted numbers to specify the country applicable.

Sample with country code:

select phone_number_to_e164('0475691444', 'NL') /* Evaluates to "+31475691444". */

Sample without country code:

select phone_number_to_e164('+31 475 691 444') /* Evaluates to "+31475691444". */

phone_number_to_national: phone number in national notation

The SQL-function phone_number_to_national cleans and rewrites a phone number in the national format of the specified country. The country may be left unspecified when using the E.164 format.

Sample with country code:

select phone_number_to_national('0475691444', 'NL') /* Evaluates to "0475 691 444". */

Sample without country code:

select phone_number_to_national('+31475691444') /* Evaluates to "0475 691 444". */

phone_number_to_international: phone number in international notation

The SQL-function phone_number_to_international cleans and rewrites a phone number in the international format. The country may be left unspecified when using the E.164 format.

Sample with country code:

select phone_number_to_international('0475691444', 'NL') /* Evaluates to "+31 475 691 444". */

Sample without country code:

select phone_number_to_international('+31475691444') /* Evaluates to "+31 475 691 444". */

phone_number_type: type of phone number

The SQL-function phone_number_type returns the line type of the phone number. The country may be left unspecified when using the E.164 format.

Sample with country code:

select phone_number_type('0475691444', 'NL') /* Evaluates to "FIXED_LINE". */

Sample without country code:

select phone_number_type('+31475691444') /* Evaluates to "FIXED_LINE". */

Sometimes, the algorithm can not determine whether it is a fixed phone number or a mobile number. In that case, the value FIXED_LINE_OR_MOBILE can be returned.

Use Case Cleaning Phone Numbers using SQL

The SQL-functions for phone numbers are typically used to validate, clean or purge phone numbers in databases. For example, the following SQL-statement removes invalid phone numbers and rewrites all other phone numbers to the international format:

update contactdata@alias
set    phone_number =
       case
       when is_phone_number(phone_number, country_code)
       then phone_number_to_international(phone_number, country_code)
       else null
       end
where  phone_number is not null
--
-- Only update rows needing work.
--
and    phone_number !=
       case
       when is_phone_number(phone_number, country_code)
       then phone_number_to_international(phone_number, country_code)
       else null
       end

Availability

String functions for phone number as SQL-functions are available starting release 22.0.619 and BETA release 22.1.183.