Selecting a substring based on a regex match

[Jake Litchfield]

I'm setting up a query in Analytics to measure Jira releases (project fix versions marked as 'Released') per team.

In our organisation, multiple teams use the same project, so their releases are distinguished with a prefix in the fix version name such as 'JA:', 'EW:' or 'SITH:'. A typical release name looks something like this:

• FY24Q4: SITH: Asset Maintenance (20%)
• FY25Q1 JA: Uplift pipelines
• EW: Target State Modernisation

I figure the easiest way to extract that team prefix given it's variability in position is with a regex, so have been trying to use the REGEXP_EXTRACT function in my select statement like so: 

SELECT REGEXP_EXTRACT(`Project fix version`.`name`, '(\b[A-Za-z]{2,4}:)') AS `Team`, ...

Using the regex checker at https://regex101.com/, my regex looks like it should work, the query in Analytics runs fine, but my result ends up with no matches.

Any help on how to solve this problem would be much appreciated!

[Robert · Accepted solution]

Hey @Jake Litchfield !

I tested this in my own instance (using the summary field instead of your field, but the regex should still apply) and I think the issue may be escaping the backslash in your expression: 

(\b[A-Za-z]{2,4}:)

when I changed it to this, I started to see the expected results:

(\\b[A-Za-z]{2,4}:)

The expression I used in testing, in case you want to try it out, was: 

SELECT REGEXP_EXTRACT(`Issue`.`summary`,'\\S+ (\\S+):') AS `Summary`,

Hope this helps! 

[Jake Litchfield]

Hi Robert, that has done just the trick, thanks very much!