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