Extract type and phone number from string

Question

I have a varchar which can have these values:

"Office: (415) 438-4437"
"Office: (602) 740-6409 Fred Cohen"
"Mobile: (707) 888-572"
"Phone: (707) 472-0982"

I need to separate these values as phone_type and phone columns.

Should look like these

phone type	phone
Office	(415) 438-4437
Office	(602) 740-6409
Mobile	(707) 888-572
Phone	(707) 472-0982

Here my code

select phone from core.person;

I can have more data after the phone number, but Just need to pick the first word of the string which is the phone_type and the phone which is (###) ###-####, how to do that?

Answer 1

May be as simple as:

SELECT split_part(phone, ': ', 1)                  AS phone_type
     , rtrim(substring(phone, '\([\d\) -]+'))      AS phone
     , substring(phone, '\(\d{3}\) \d{3}-\d{3,4}') AS phone_strict
FROM   core.person;

db<>fiddle here

The second variant is stricter. (But still not strictly (###) ###-#### like you wrote, which seems wrong about the trailing 4 digits.)

We could use a regular expression for the phone_type, too, but split_part() is simpler and faster.

About split_part():

• Split comma separated column data into additional columns

About substring() and regular expressions:

• Postgres - Return default value if regex match fails

Answer 2

May be try REGEXP_SUBSTR like the following:

--splitting the text with delimiter as ':'
--removing " to clean the remaining text
    Select replace(REGEXP_SUBSTR('"Office: (415) 438-4437"', '[^:]+', 1, 1), '"') as Phone_Type,
    replace(REGEXP_SUBSTR('"Office: (415) 438-4437"', '[^:]+', 1,2), '"') as Phone  from Dual;