Skip to content

is_phone_number_valid

is_phone_number_valid(phone_number, options)

Description

Return if phone_number is valid 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.

Usage

Call or Deploy is_phone_number_valid ?
Call is_phone_number_valid directly

The easiest way to use bigfunctions

  • is_phone_number_valid function is deployed in 39 public datasets for all of the 39 BigQuery regions.
  • It can be called by anyone. Just copy / paste examples below in your BigQuery console. It just works!
  • (You need to use the dataset in the same region as your datasets otherwise you may have a function not found error)

Public BigFunctions Datasets

Region Dataset
eu bigfunctions.eu
us bigfunctions.us
europe-west1 bigfunctions.europe_west1
asia-east1 bigfunctions.asia_east1
... ...
Deploy is_phone_number_valid in your project

Why deploy?

  • You may prefer to deploy is_phone_number_valid in your own project 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).
  • Get started by reading the framework page

Deployment

is_phone_number_valid function can be deployed with:

pip install bigfunctions
bigfun get is_phone_number_valid
bigfun deploy is_phone_number_valid

Examples

1. Check an international phone_number (starting with +)

select bigfunctions.eu.is_phone_number_valid("+33123456789", null)

select bigfunctions.us.is_phone_number_valid("+33123456789", null)

select bigfunctions.europe_west1.is_phone_number_valid("+33123456789", null)

+----------+
| is_valid |
+----------+
| true     |
+----------+

2. Check a national phone_number

select bigfunctions.eu.is_phone_number_valid("0123456789", json '{"defaultCountry": "FR"}')

select bigfunctions.us.is_phone_number_valid("0123456789", json '{"defaultCountry": "FR"}')

select bigfunctions.europe_west1.is_phone_number_valid("0123456789", json '{"defaultCountry": "FR"}')

+----------+
| is_valid |
+----------+
| true     |
+----------+

3. If no phone number is found in phone_number, it returns false

select bigfunctions.eu.is_phone_number_valid("Hello!", null)

select bigfunctions.us.is_phone_number_valid("Hello!", null)

select bigfunctions.europe_west1.is_phone_number_valid("Hello!", null)

+----------+
| is_valid |
+----------+
| false    |
+----------+

4. By default, if the given phone_number text contains a valid phone number among other text, it returns true.

select bigfunctions.eu.is_phone_number_valid("Hello +33123456789 !", null)

select bigfunctions.us.is_phone_number_valid("Hello +33123456789 !", null)

select bigfunctions.europe_west1.is_phone_number_valid("Hello +33123456789 !", null)

+----------+
| is_valid |
+----------+
| true     |
+----------+

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

select bigfunctions.eu.is_phone_number_valid("Hello +33123456789 !", json '{"extract": false}')

select bigfunctions.us.is_phone_number_valid("Hello +33123456789 !", json '{"extract": false}')

select bigfunctions.europe_west1.is_phone_number_valid("Hello +33123456789 !", json '{"extract": false}')

+----------+
| is_valid |
+----------+
| false    |
+----------+

Use cases

A common use case for the is_phone_number_valid function is cleaning and validating customer data. Imagine you have a BigQuery table containing customer information, including a phone number column. This data might have been collected from various sources and could contain errors, inconsistencies, or improperly formatted numbers.

Scenario: You want to identify valid phone numbers in your customer data to improve the accuracy of your marketing campaigns, reduce communication errors, and ensure data quality for analysis.

Implementation using is_phone_number_valid:

#standardSQL
CREATE OR REPLACE TABLE `your_project.your_dataset.cleaned_customer_data` AS
SELECT *
FROM `your_project.your_dataset.customer_data`
WHERE bigfunctions.your_region.is_phone_number_valid(phone_number, json '{"defaultCountry": "US"}'); -- Replace "US" with the appropriate default country if needed.

-- Alternatively, to handle various international numbers without a default country:
CREATE OR REPLACE TABLE `your_project.your_dataset.cleaned_customer_data_international` AS
SELECT *
FROM `your_project.your_dataset.customer_data`
WHERE bigfunctions.your_region.is_phone_number_valid(phone_number, NULL); --  Handles international numbers starting with "+"

-- Or, to find potentially valid numbers embedded within other text:
CREATE OR REPLACE TABLE `your_project.your_dataset.potentially_valid_numbers` AS
SELECT *
FROM `your_project.your_dataset.customer_data`
WHERE bigfunctions.your_region.is_phone_number_valid(notes_field, NULL); -- Extract phone numbers from a text field like 'notes'

Explanation:

  • your_project.your_dataset.customer_data: Your original table with customer information, including a phone_number column (and potentially other text fields that might contain phone numbers).
  • bigfunctions.your_region.is_phone_number_valid(phone_number, ...): This calls the BigFunction, passing the phone_number column and optional parameters.
    • Using json '{"defaultCountry": "US"}' helps validate national numbers without the "+" prefix assuming they are from the US.
    • Using NULL as the second argument allows validation of international numbers (starting with "+") and attempts to extract phone numbers embedded in other text. You can further refine this with the extract option in the JSON.
  • WHERE clause: Filters the customer data, keeping only rows where the is_phone_number_valid function returns true.

Benefits:

  • Data Quality: Ensures your customer data contains only valid and consistently formatted phone numbers.
  • Improved Communication: Reduces errors in SMS marketing, phone calls, and other communication efforts.
  • Accurate Analysis: Provides reliable data for customer segmentation, targeting, and other analytical tasks.
  • Cost Savings: Avoids wasted resources on trying to contact invalid or unreachable phone numbers.

This use case demonstrates how is_phone_number_valid can be incorporated into a data cleaning workflow to maintain data integrity and improve the overall quality of your customer data in BigQuery. Remember to replace "your_project", "your_dataset", "your_region", and "customer_data" with your actual values. And adjust the optional parameters of the is_phone_number_valid function as needed for your specific data and requirements.

Need help or Found a bug?
Get help using is_phone_number_valid

The community can help! Engage the conversation on Slack

We also provide professional suppport.

Report a bug about is_phone_number_valid

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.

We also provide professional suppport.

Show your ❤ by adding a ⭐ on