Using regexp for pulling multiple values out a text string

cmoreno

Hello, I need some help pulling out email addresses from a text string.

My goal is to place these multiple emails listed in the 'Description' column in a separate column, where they'll be separated by ','. Is there way for the search to end before the Sent Date info?

Thank you!

GrantSmith

Try something like this to make it a bit simpler:

REPLACE(REGEXP_REPLACE(SPLIT_PART(SPLIT_PART(``, 'Sent:', 1), 'To: ', 2), '[^<]+<([^>]+>)', '$1'), '>', ',')

SPLIT_PART(``, 'Sent:', 1)

Gets evertying in your string before 'Sent: ' - drops everything after it

SPLIT_PART(SPLIT_PART(``, 'Sent:', 1), 'To: ', 2)

Gets the entire text in between the To: and Sent: values so you're left with just the email addresses and the aliases

REGEXP_REPLACE(…, '[^<]+<([^>]+>)', '$1')

Finds all occurrences of the email addresses and any values outside of the < > and drops it in favor of what's in the < >. This keeps the trailing > so we can replace it with REPLACE in the last step with commas.

GrantSmith

Are you wanting the From and To both to be included or just the To? Will the From and To fields only have a single email or would there be multiple emails?

cmoreno

@GrantSmith I'm needing just the emails that would be listed in the To field. The emails listed in To field can have multiple email addresses listed. Thank you for your help!

GrantSmith

Try something like this to make it a bit simpler:

REPLACE(REGEXP_REPLACE(SPLIT_PART(SPLIT_PART(``, 'Sent:', 1), 'To: ', 2), '[^<]+<([^>]+>)', '$1'), '>', ',')

SPLIT_PART(``, 'Sent:', 1)

Gets evertying in your string before 'Sent: ' - drops everything after it

SPLIT_PART(SPLIT_PART(``, 'Sent:', 1), 'To: ', 2)

Gets the entire text in between the To: and Sent: values so you're left with just the email addresses and the aliases

REGEXP_REPLACE(…, '[^<]+<([^>]+>)', '$1')

Finds all occurrences of the email addresses and any values outside of the < > and drops it in favor of what's in the < >. This keeps the trailing > so we can replace it with REPLACE in the last step with commas.

cmoreno

@GrantSmith It worked, thank you!