Querying columns with special characters

[JamieN]

I'm trying to do a select on a column with special characters in Databricks sql.

I've tried the following

%sql
select ex$col 
from database.table
limit 10;
 
%sql
select `ex$col `
from database.table
limit 10;

They both don't work and will return "ex does not exist. Did you mean the following/ .... "

Is there a way to do this? or is the functionality not currently supported?

[LandanG]

It might depend on how your table was created. I was able to get the below to work:

CREATE OR REPLACE TABLE database.special_chars (`ex$col` int);
INSERT INTO database.special_chars VALUES (1);
 
SELECT `ex$col`
FROM database.special_chars
LIMIT 10;

[JamieN · Accepted solution]

Many thanks for your reply.

Ah, we are creating the table using a delta location

 
s = f"create table {database}.{dataset_name} using delta location '{location}'"
spark.sql(s)

I can still query the special character using pyspark which good for me now, but a lot of our users will want to use sql. The only option seems to be to change the schema.

[arun_pamulapati]

@LandanG . The issue is with %sql .

[Anonymous]

Hi @Jamie Nathan​ 

Hope all is well!

Just wanted to check in if you were able to resolve your issue and would you be happy to share the solution or mark an answer as best? Else please let us know if you need more help. 

We'd love to hear from you.

Thanks!