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?

I took the liberty to fix your results to match your input. Undo if I got that wrong. — Erwin Brandstetter, Apr 07 '22 at 22:41
*Here my code*: What code? The single bit of SQL you posted makes no effort to do anything that your question asks about doing. What effort have you made to actually solve the problem? — Ken White, Apr 07 '22 at 22:47

Erwin Brandstetter · Accepted Answer · 2022-04-07T22:38:36.163

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

score 0 · Answer 2 · answered Apr 08 '22 at 02:33

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;

Extract type and phone number from string

2 Answers2