'Extract type and phone number from string

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?

sqlregexpostgresqlsplit


Solution 1:[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():

About substring() and regular expressions:

Solution 2:[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;

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2 Ashish Samarth

Related Questions