CODEWITHSUNDEEP
regex
Biswankar Das
Biswankar Das

Reputation: 305

Find if text contains phone numbers in Big Query

enter code hereI am trying to find if a text column on Big Query has phone numbers in them.
I am currently using the following code:

Input table :

text_field
Hello please call us on +1 123 456 789
Please allow 2 days for the purchase to reflect in your wallet

Code:

select 
    text_field
from 
    table
where
and REGEXP_CONTAINS(text_field, r'[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]') = True

Desired table :

text_field
Hello please call us on +1 123 456 789

However, I see many different formats of phone numbers written in many different ways. Example: +1 123 345 789, + 123-456-789 etc
Is there any general regular expression I could use to detect any phone number in the text field?

Upvotes: 1

Views: 370

Answers (1)

максим ильин
максим ильин

Reputation: 194

if phone numbers are separated by commas, then you can use the following regular expression '[^,]+' If the phone numbers are not structured, then the search is difficult. Or use ^\+?[178][-\(]?\d{3}\)?-?\d{3}-?\d{2}-?\d{2}$ only I don't know if the database supports such regular expressions

Upvotes: 0

Related Questions