How do I extract the numbers after a phrase (xvth) and replace the phrase with "Group-"?

user_15224121 0
Viewed 101

I have data being exported from BigQuery into Google Data Studio one field contains a username like the following.

  • xvth20-00-tt-wr
  • xvth27-00-pt-px

The first 4 characters (xvth) are always the same and the numbers that follow (xvth) correspond to a group. Multiple usernames will contain the same numbers after those characters but the rest of the string from 00- and on will be different. What I'm trying to do is extract the numbers that follow the 4 characters and create a new field that looks like the following.

  • Group-20
  • Group-27

I've tried the following REPLACE(SUBSTR(Users,1, 6), 'xvth20', 'Group-20') and I will have to create one for every condition which seems like too much. Also the data will keep growing so I wouldn't want to keep going in and adding another function. Is there an easier way to do this?

re2 google-data-studio regex
1 Answers

Either of the below REGEXP_REPLACE Calculated Fields will replace xvth with Group-, immediately followed by the respective captured numbers; Calculated Field #1 uses a Raw Literal, indicated by the letter r which requires a single \ to escape special RegEx characters whereas Calculated Field #2 requires \\ to escape a Google Data Studio RegEx as it does not use a Raw Literal:

1) With r (Raw Literal)

REGEXP_REPLACE(Users, r"^xvth(\d+).*", r"Group-\1")

2) Without r (Raw Literal)

REGEXP_REPLACE(Users, "^xvth(\\d+).*", "Group-\\1")

Editable Google Data Studio Report (Embedded Google Sheets Data Source) and a GIF to elaborate:

user_128925530
Related