regex_replace value that starts with char, followed by integers BigQuery SQL-CodePudding

I have a table in BigQuery that I need to clean up. In column content_column I have values like:

content_column
/a68786776
/g263647
/g47664
/galleries
/music

I want to replace all values that start with /g followed by any number of integers to a string gallery. The desired output is:

content_column
/a68786776
gallery
gallery
/galleries
/music

I have tried the following:

SELECT regexp_replace(content_column,r'\/(\w \d )\/', "gallery") as content_column
FROM `my_table`

This works but it also converts /a68786776 to gallery. I tried to replace (\w \d ) to (\^g\d ) but that didn't convert anything to gallery.

Thanks for help in advance.

CodePudding user response：

If you want to replace the full match with gallery you don't need a capture group in the pattern.

This pattern \w \d matches 1 word chars and 1 digits, which is a minimum of 2 characters and does not specify a g char
This pattern \^g\d matches ^g and 1 digits.

Instead you can match / and 1 or more digits:

r'/g\d