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)

Join 100K+ Data Experts: Register Now & Grow with Us!

Excited to expand your horizons with us? Click here to Register and begin your journey to success!

Already a member? Login and join your local regional user group! If there isn’t one near you, fill out this form and we’ll create one for you to join!