topic Re: Databricks SQL - Unable to Escape Dollar Sign ($) in Column Name in Data Engineering

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

VVM — Mon, 27 Feb 2023 23:12:56 GMT

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.

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

Lakshay — Tue, 28 Feb 2023 10:55:29 GMT

Hi @Patrick Mascari , Just to understand the issue, you have a column named "$id" in your table and when you do "select $id from table" then it is failing. Is my understanding correct?

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

Tayyab_Vohra — Tue, 28 Feb 2023 13:51:07 GMT

Hi @Patrick Mascari can you try by using the SQL() function, I have written the code below with an example please check if that works for you.

You can extract any escaped sequence in the column

data = spark.createDataFrame([("Alberto", 101), 
                              ("Dakota", 202), 
                              ("Kumar", 330),
                              ("Siva", 4),
                              ("Sree", 5),
                              ("Kavin", 6)], 
                                   ["Name", "$id"])
 
data.createOrReplaceTempView("temp_table")
sql("select `$id` as id, name from temp_table").show()

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

VVM — Tue, 28 Feb 2023 16:52:29 GMT

I want to build a table using the CREATE TABLE statement in Databricks SQL. But I can't name the column $id because `$id` does not work (in spite of Databricks' SHOW CREATE saying it should)

I understand that I can probably work around this by swapping to python/scala to run a spark query. If that's the only resort, then Databricks surely has a bug that needs reported and resolved..

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

NateAnth — Tue, 28 Feb 2023 17:01:36 GMT

Thank you for engaging with us on Databricks Community!

When you say you are trying this in Databricks SQL, I do not understand why the create table command is not working for you. Please see my working example with $'s in the column names:

To further narrow down what you are facing. Please clarify if you are attempting this in the Databricks SQL persona in the UI via the SQL editor (like in my screenshot above), or in a Notebook within the Data Science & Engineering persona. Screenshots would be helpful to further understand.

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

VVM — Tue, 28 Feb 2023 17:17:48 GMT

Yes, this is in a Notebook. You can see below that it fails because both instances get detected as query snippets and swapped out with blank, so the query effectively becomes CREATE TABLE my_temp_table( `` int, `` string )

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

NateAnth — Tue, 28 Feb 2023 17:28:50 GMT

Thank you for clarifying. I too see the same behavior and have brought this to the attention of our Notebook team to further investigate.

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

NateAnth — Tue, 28 Feb 2023 19:09:52 GMT

Thank you @Patrick Mascari for bringing this to our attention, the appropriate engineering and product team is aware of the issue and will prioritize it accordingly to be addressed in the future. There is no ETA that can be provided at this time.

In the mean time, consider the following work arounds:

Use spark.sql() in a python cell
Use the Databricks SQL Editor in the SQL Persona

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

_Dusk — Fri, 31 Mar 2023 05:37:02 GMT

I am working with an imported cdc table.

I can select the column `__$operation` in the query editor but not in a SQL notebook.

This is so annoying.

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

Lee78 — Thu, 03 Aug 2023 04:48:10 GMT

Is this issue resolved?

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

Whit127 — Tue, 08 Aug 2023 19:30:48 GMT

This has not been resolved from what I can see. Databricks team, any progress updates here? Its been 6 months.

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

AlexRodriges202 — Thu, 07 Dec 2023 21:13:13 GMT

wow

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

Casper-Bang — Mon, 08 Apr 2024 11:24:25 GMT

What is the status on this bug report? its been over a year now.

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

JR-20773 — Tue, 03 Sep 2024 14:15:25 GMT

What is the status of this bug? This is affecting user experience.

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

rgower — Wed, 07 May 2025 14:41:07 GMT

+1 here - hoping to hear any updates.

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

ryry_luma — Thu, 07 Aug 2025 02:58:08 GMT

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')

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

andreasmarkmann — Thu, 07 Aug 2025 23:55:14 GMT

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`'