REGEXP_EXTRACT specific string to extract year or month in Google Data Studio

user_15900852 0
Viewed 250

I'm trying to categorize my sites but they have not always the same uri-structure so I want to extract the year in one column and in the second one I want to extract the month.

The results should be year and months in seperate columns/fields:

url year months
/www.site.com/path1/resort/2021/02/sitename 2021 02
/www.site.com/path1/2021/02 2021 02
/www.site.com/path1/2020/11-12 2020 11-12
/www.site.com/path1/2020/07-08 2020 07-08
/www.site.com/path1/resort/ null null

the following regex for the year worked:

REGEXP_EXTRACT(url,'([0-9]{4})') >> result: 2020, null etc.

but the regex for the month didnt extract only the months:

REGEXP_EXTRACT(url,'((?:[0-9]{4}/)[0-9]+.?[0-9]*/)') >> result: 2020/11-12/,2021/02/, null etc.

Thanks for the help in advance.

re2 google-data-studio regex
1 Answers

You can use

(?:^|/)((?:19|20)[0-9]{2})/((?:0?[1-9]|1[0-2])(?:-(?:0?[1-9]|1[0-2]))?)(?:/|$)

See the regex demo.

If you need to capture only once per a match, replace the capturing group with non-capturing, or remove the extra pattern:

REGEXP_EXTRACT(col_url, '(?:^|/)((?:19|20)[0-9]{2})(?:/|$)') as Year
REGEXP_EXTRACT(col_url, '(?:^|/)((?:0?[1-9]|1[0-2])(?:-(?:0?[1-9]|1[0-2]))?)(?:/|$)') as Month

Details:

  • (?:^|/) - string start or /
  • ((?:19|20)[0-9]{2}) - Group 1: a year, 19 or 20 followed with any two digits
  • / - a / char
  • ((?:0?[1-9]|1[0-2])(?:-(?:0?[1-9]|1[0-2]))?) - Group 2 (month): an optional 0 and then 1 to 9, or 1 and then 0 to 2 (00-12), and then an optional occurrence of - and the same month pattern
  • (?:/|$) - / or end of string.
user_38329700
Related