Skip to content

bigfunctions > is_phone_number_valid

is_phone_number_valid

Call or Deploy is_phone_number_valid ?

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

  • This is_phone_number_valid 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

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.

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    |
+----------+

Need help using is_phone_number_valid?

The community can help! Engage the conversation on Slack

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

Found a bug using 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.

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

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.

Spread the word

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

Share on Add a on