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?
CodePudding user response:
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:
CodePudding user response:
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;