Databricks SQL - Unable to Escape Dollar Sign ($) in Column Name

[VVM]

It seems that due to how Databricks processes SQL cells, it's impossible to escape the $ when it comes to a column name.

I would expect the following to work:

%sql
SELECT 'hi' `$id`

The backticks ought to escape everything. And indeed that's exactly what Databricks thinks should happen, too. I was able to create a table outside of SQL commands (from a Dataframe) that had a column called $id . When I did a SHOW CREATE on that table, Databricks suggested using `$id` .

But what seems to happen is that Databricks seizes upon the sight of any $X to do Query Snippeting. So I end up with a blank column name instead.

Query Snippets only seem to respect the \ character as a means of escaping. But this does not bode well in conjunction with the backtick all-encompassing escape method, as the backticks will interpret the \ character literally, leaving me with a fieldname of \$id .

Does anyone have a workaround? If not, how can this be raised to a bug report? Thanks.

[ryry_luma]

As someone who spent a few hours this morning bashing my head against this and this is the only relevant thread I could see. A solution I found to inserting a '$' in a query string in a notebook was to concat(chr(36), 'xxx')

[andreasmarkmann]

I needed a $ character in the context of Java format strings in the SQL printf function.

With some trial and error, I found workarounds:

A trailing backslash escapes the dollar character successfully, like so
SELECT printf('%1$\s, %1$\s', 'hi') AS test
Column names can be escaped normally when wrapped in a statement string and executed with EXECUTE IMMEDIATE:
EXECUTE IMMEDIATE 'SELECT printf(\'%1$\s, %1$\s\', \'hi\') AS `\$test`'