I am trying to do a regular expression replace in a Databricks notebook.
The following query works fine in a regular query (i.e. not running it in a cell in a notebook):
select regexp_replace('abcd', '^(.+)c(.+)$', '$1_$2') --normally outputs ab_d
However, if you run it in a notebook cell, it does not work correctly. Instead the query output is an empty string and a small warning is shown:
SQL query contains $ parameter. Migrate to the parameter marker syntax, :param:
Parameter values:
2: <empty>
1_: <empty>
So it is incorrectly interpreting the back references ($1 and $2) as parameters that are not supplied via a widget.
I can work around this issue by doing some string concatenation like so (so that $ is never followed by a number):
select regexp_replace('abcd', '^(.+)c(.+)$', '$'||'1_$'||'2')
but that feels like a hack/kludge. Is there a better way to avoid this issue or escape the dollar sign?
