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

eriodega — Thu, 29 Aug 2024 15:30:25 GMT

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?

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

filipniziol — Thu, 29 Aug 2024 19:02:53 GMT

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