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 inbigfunctions
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 aphone_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 thephone_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 theextract
option in the JSON.
- Using
WHERE
clause: Filters the customer data, keeping only rows where theis_phone_number_valid
function returnstrue
.
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!