cancel
Showing results for 
Search instead for 
Did you mean: 
Warehousing & Analytics
Engage in discussions on data warehousing, analytics, and BI solutions within the Databricks Community. Share insights, tips, and best practices for leveraging data for informed decision-making.
cancel
Showing results for 
Search instead for 
Did you mean: 

Not able to escape `-` in external connected tablename

acurus
New Contributor II

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?

 

3 REPLIES 3

Kaniz_Fatma
Community Manager
Community Manager

Hi @acurus, When dealing with table names containing special characters, such as the hyphen (“-”), you can use square brackets to enclose the table name. This approach is commonly used in SQL databases to handle irregular identifiers. 

 

Here are some ways to work with table names that include special characters:

 

Using Square Brackets:

  • Enclose the table name in square brackets, like this:CREATE TABLE [Table-One] (ID INT NOT NULL);
  • Similarly, when querying the table, use:SELECT * FROM [Table-One];
  • Note that while this approach works, it’s considered a best practice to avoid using special characters in table names whenever possible.

Double Quotes (For Oracle):

  • In Oracle databases, you can use double quotes to create a table with special characters:CREATE TABLE "Table-With-Special-Character" (ID INT NOT NULL);
  • When querying the table, use:SELECT * FROM "Table-With-Special-Character";
  • Remember that this method is case-sensitive, so ensure the table name matches exactly.

Escape Characters:

  • You can use the ESCAPE keyword to search for specific characters within column values. For example:SELECT * FROM TestTable WHERE Col1 LIKE '%! [blog.]%' ESCAPE '!'; Replace the exclamation mark (“!”) with any other special character you want to search for.

Remember that while these techniques allow you to work with tables containing special characters, it’s generally recommended to avoid using irregular identifiers to maintain code readability and compatibility across different database clients.

 

If possible, consider renaming the table to a more standard format without special characters.

acurus
New Contributor II

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.

martinschou
New Contributor II

Had the same issue when querying a table with the - character in the table name. Got the errorIncorrect 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)

Connect with Databricks Users in Your Area

Join a Regional User Group to connect with local Databricks users. Events will be happening in your city, and you won’t want to miss the chance to attend and share knowledge.

If there isn’t a group near you, start one and help create a community that brings people together.

Request a New Group