REGEX_EXTRACT in Google SQL-CodePudding

I have a string that looks like this:

DadC - Review Vid - Vid - Eng - Owner: TC

How can I regex extract using SQL the first word and the last word after Owner in one capture group?

Currently in google SQL I have a query that looks like this to extract the last word: SELECT *, REGEX_EXTRACT(column_name, r'(\bOwner.*\b)') but how do I add the first word in one capture group?

I want an output that looks like this: 'DadC - TC'

CodePudding user response：

Since it's impossible to capture non-contiguous parts of a string into a single capturing group, I suggest capturing your two desired parts separately and then concatenating resulting strings.

SELECT *, 
       REGEX_EXTRACT(column_name, r'^(\w )\b') || ' - ' ||
       REGEX_EXTRACT(column_name, r'\bOwner:\s*(\w )\b')

The above is untested but should give you good idea about the suggested approach.

Here's a good general read on your case of capturing: Non-contiguous text Capture with Regular Expressions. How can I do it?