I want to separate phone number into 2 fields in bigquery using regexp expression

1. PhoneNumber
2. Business Extension

The input looks like

The expected output should look like

CodePudding user response：

As long a special character (and not a -) is between the phoneNumber and the extension:

with tbl as (
Select "408-697-4639 x. 301" phone
Union ALL Select "563-232-1400x6740"
Union ALL Select "248-456-8250ext. 100")

SELECT *,REGEXP_EXTRACT(phone, r"^[0-9\-] ") as phoneNumber,
REGEXP_EXTRACT(phone, r"\d $") extension
From tbl