Skip to content

bigfunctions > phone_number_info

phone_number_info

Call or Deploy phone_number_info ?

✅ You can call this phone_number_info bigfunction directly from your Google Cloud Project (no install required).

  • This phone_number_info function is deployed in bigfunctions GCP project in 39 datasets for all of the 39 BigQuery regions. You need to use the dataset in the same region as your datasets (otherwise you may have a function not found error).
  • Function is public, so it can be called by anyone. Just copy / paste examples below in your BigQuery console. It just works!
  • You may prefer to deploy the BigFunction in your own project if you want to build and manage your own catalog of functions. This is particularly useful if you want to create private functions (for example calling your internal APIs). Discover the framework

Public BigFunctions Datasets:

Region Dataset
eu bigfunctions.eu
us bigfunctions.us
europe-west1 bigfunctions.europe_west1
asia-east1 bigfunctions.asia_east1
... ...

Description

Signature

phone_number_info(phone_number, options)

Description

Get phone_number info such as:

  • country,
  • isValid,
  • etc

using libphonenumber-js library.

Argument options can be null or must be a json with the following keys: defaultCountry, defaultCallingCode and extract as described in the library documentation.

Examples

1. Get info about an international phone_number (starting with +)

select bigfunctions.eu.phone_number_info('+33123456789', null)
select bigfunctions.us.phone_number_info('+33123456789', null)
select bigfunctions.europe_west1.phone_number_info('+33123456789', null)
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| info                                                                                                                                                                                                                                                                                                                                                                                       |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| {
  "isPossible": true,
  "isValid": true,
  "parseError": null,
  "country": "FR",
  "countryCallingCode": "33",
  "formattedInternational": "+33 1 23 45 67 89",
  "formattedNational": "01 23 45 67 89",
  "isNonGeographic": false,
  "nationalNumber": "123456789",
  "number": "+33123456789",
  "possibleCountries": ["FR"],
  "type": "FIXED_LINE",
  "uri": "tel:+33123456789"
}
 |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

2. Get info about a national phone_number

select bigfunctions.eu.phone_number_info('0123456789', json '{"defaultCountry": "FR"}')
select bigfunctions.us.phone_number_info('0123456789', json '{"defaultCountry": "FR"}')
select bigfunctions.europe_west1.phone_number_info('0123456789', json '{"defaultCountry": "FR"}')
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| info                                                                                                                                                                                                                                                                                                                                                                                       |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| {
  "isPossible": true,
  "isValid": true,
  "parseError": null,
  "country": "FR",
  "countryCallingCode": "33",
  "formattedInternational": "+33 1 23 45 67 89",
  "formattedNational": "01 23 45 67 89",
  "isNonGeographic": false,
  "nationalNumber": "123456789",
  "number": "+33123456789",
  "possibleCountries": ["FR"],
  "type": "FIXED_LINE",
  "uri": "tel:+33123456789"
}
 |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

3. If no phone number is found in phone_number argument, a reason in given in parseError

select bigfunctions.eu.phone_number_info('Hello!', null)
select bigfunctions.us.phone_number_info('Hello!', null)
select bigfunctions.europe_west1.phone_number_info('Hello!', null)
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| info                                                                                                                                                                                                                                                                                                                               |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| {
  "isPossible": false,
  "isValid": false,
  "parseError": "NOT_A_NUMBER",
  "country": null,
  "countryCallingCode": null,
  "formattedInternational": null,
  "formattedNational": null,
  "isNonGeographic": null,
  "nationalNumber": null,
  "number": null,
  "possibleCountries": null,
  "type": null,
  "uri": null,
}
 |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

4. By default, if the given phone_number text contains a phone number among other text, it will be extracted.

select bigfunctions.eu.phone_number_info('Hello +33123456789 !', null)
select bigfunctions.us.phone_number_info('Hello +33123456789 !', null)
select bigfunctions.europe_west1.phone_number_info('Hello +33123456789 !', null)
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| info                                                                                                                                                                                                                                                                                                                                                                                       |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| {
  "isPossible": true,
  "isValid": true,
  "parseError": null,
  "country": "FR",
  "countryCallingCode": "33",
  "formattedInternational": "+33 1 23 45 67 89",
  "formattedNational": "01 23 45 67 89",
  "isNonGeographic": false,
  "nationalNumber": "123456789",
  "number": "+33123456789",
  "possibleCountries": ["FR"],
  "type": "FIXED_LINE",
  "uri": "tel:+33123456789"
}
 |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

5. To consider that phone_number cannot have additional text use extract: false as option

select bigfunctions.eu.phone_number_info('Hello +33123456789 !', json '{"extract": false}')
select bigfunctions.us.phone_number_info('Hello +33123456789 !', json '{"extract": false}')
select bigfunctions.europe_west1.phone_number_info('Hello +33123456789 !', json '{"extract": false}')
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| info                                                                                                                                                                                                                                                                                                                               |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| {
  "isPossible": false,
  "isValid": false,
  "parseError": "NOT_A_NUMBER",
  "country": null,
  "countryCallingCode": null,
  "formattedInternational": null,
  "formattedNational": null,
  "isNonGeographic": null,
  "nationalNumber": null,
  "number": null,
  "possibleCountries": null,
  "type": null,
  "uri": null,
}
 |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Need help using phone_number_info?

The community can help! Engage the conversation on Slack

For professional suppport, don't hesitate to chat with us.

Found a bug using phone_number_info?

If the function does not work as expected, please

  • report a bug so that it can be improved.
  • or open the discussion with the community on Slack.

For professional suppport, don't hesitate to chat with us.

Use cases

A customer service department stores customer phone numbers in a BigQuery table. They want to clean up the data and enrich it with location information. The phone_number_info function can be used to accomplish this.

Use Case Scenario:

The table customer_data contains a column phone with various formats of phone numbers, including some with extra characters or missing country codes.

Example BigQuery SQL:

SELECT
    phone,
    bigfunctions.us.phone_number_info(phone, JSON '{"defaultCountry": "US"}') AS phone_info
FROM
    `project_id.dataset_id.customer_data`;

Explanation:

  1. bigfunctions.us.phone_number_info(phone, JSON '{"defaultCountry": "US"}'): This calls the phone_number_info function.

    • We're using the us dataset because our project is in the US multi-region. Choose the appropriate regional or multi-regional dataset for your project's location.
    • phone is the column containing the phone number string.
    • JSON '{"defaultCountry": "US"}' provides the optional defaultCountry parameter. This is important for correctly interpreting phone numbers that don't start with a "+" and country code. It assumes any number without a "+" is a US number. You would change this to match the expected default country for your data.
  2. AS phone_info: This assigns the output of the function to a new column named phone_info. The output is a JSON structure.

Benefits:

  • Standardization: The function parses and standardizes the phone numbers into a consistent international format (number field in the JSON output), even if the original data was messy.
  • Validation: The isValid field in the JSON output indicates whether the phone number is valid according to international standards. This allows for identifying and correcting invalid numbers.
  • Enrichment: The function provides additional information like country and type (e.g., mobile, fixed line). This data can be used for segmentation, analytics, and reporting.
  • Data Cleaning: You can use the output to filter out invalid numbers:
SELECT
    phone
FROM
    `project_id.dataset_id.customer_data`,
    UNNEST(bigfunctions.us.phone_number_info(phone, JSON '{"defaultCountry": "US"}')) AS phone_info
WHERE phone_info.isValid = TRUE;

This example demonstrates how to use the phone_number_info function to clean, validate, and standardize phone number data in BigQuery, enabling better data quality and more insightful analysis. Remember to adjust the dataset and defaultCountry parameter based on your project's location and the characteristics of your data.

Spread the word

BigFunctions is fully open-source. Help make it a success by spreading the word!

Share on Add a on