BigQuery REGEXP_REPLACE referencing capture group in the replacement expression

Question

I'm very new to Regex so this may seem a very dumb question.

I've been playing around with captured groups in Google Sheets, without any problems, but when I try and apply it to BigQuery, it doesn't seem to work and I can't find out how to implement the syntax.

I looked round and this seems to be the closest answer, but I can't make it work: Find and replace using regular expression, group capturing, and back referencing

I want to reference a capture group in the replacement expression to either extract or replace £ 1,000.23 in this text:

random text £ 1,000.23 other text

I've got 3 groups: (.+) (£\ *[\d\.\,]+) (.+)

It may not be the best example, but I really want to understand how to use a capture group in the replacement part so I'm not looking for an alternative solution.

The code below literally returns '$2' rather than '£ 1,000.23'.

SELECT
  note,
  REGEXP_REPLACE(note,r'(.+)(£\ *[\d\.\,]+)(.+)','$2') AS note2
FROM
  `project.dataset.table`
LIMIT
  100

Thanks for any help!

Answer 1

According to the replacement note in the doc, I think the following should work:

SELECT
  note,
  REGEXP_REPLACE(note,r'(.+)(£\ *[\d\.\,]+)(.+)','\\2') AS note2
FROM
  `project.dataset.table`
LIMIT
  100