Escaping $ (dollar sign) in a regex backreference in notebook (so not seen as a parameter)

[eriodega]

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>

The warning includes a link: https://docs.databricks.com/notebooks/widgets.html#use-widget-values-in-spark-sql-and-sql-warehouse

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?

[filipniziol · Accepted solution]

Hi, just put a backslash before $ as an escape character: