topic Not able to escape `-` in external connected tablename in Warehousing & Analytics

Not able to escape `-` in external connected tablename

acurus — Tue, 28 Nov 2023 09:32:23 GMT

We are having some issues with getting data from some tables with the character `-` in their tablename.

We are connected to the database with an SQL server connection, and the database is (as far as we know) a Microsoft Azure SQL Database. We do not have access to the database other than credentials to read a specific schema.

When we either try to view sample data in Unity Catalog, or run a query like:

"SELECT * FROM `tablename$i-comm`;"

We get the error: Incorrect syntax near '-'.

We have tried to escape special characters in the table name in various ways without luck.

`tablename$i-comm`
`[tablename$i-comm]`
"tablename$i-comm"
`tablename$i/-comm`
`tablename$i\-comm`
etc

Does anyone have any idea of how we can read data from a table with a name like that?

Re: Not able to escape `-` in external connected tablename

acurus — Wed, 29 Nov 2023 06:41:26 GMT

Hi Kaniz and thank you for the thourogh answer!

As I mentioned in my post I have tried square brackes around the table name without luck. As also noted, it's not an Oracle database, and I have tried double quotes around the table name without luck.

I did not try the "ESCAPE" keyword as that is only for "LIKE" queries as far as I know. I did give it a go now, and was not able to get it to work.

I fully agree that using special characters in table names is bad practice, unfortunally, as mentioned in the post, I do not have control over the tables, and can not change their names.

Re: Not able to escape `-` in external connected tablename

martinschou — Wed, 12 Jun 2024 07:53:31 GMT

Had the same issue when querying a table with the - character in the table name. Got the error: Incorrect syntax near '-'.

Got the error on Databricks runtime version: 13.2 (includes Apache Spark 3.4.0, Scala 2.12)

No error when using Databricks runtime version: 14.2 (includes Apache Spark 3.5.0, Scala 2.12)