CODEWITHSUNDEEP
sqlcsvjdbc
Jacob van Lingen
Jacob van Lingen

Reputation: 9537

Escaping columns with special chars in SQL JDBC

I got a csv file that I want to import with the CsvJdbc driver. I can´t change the csv file, as it is an export from an extern program.

Unfortunately the columns got [] around their name, like [organisation_id]

When I want to query the columns, I got a syntax error as [, ] chars are not supported. How can I escape them?

N.B. I already tried \\[organisation_id]\\, \[organisation_id\], //[organisation_id//], /[organisation_id/]

Upvotes: 0

Views: 483

Answers (2)

Simon C
Simon C

Reputation: 2017

This problem is solved by CsvJdbc Feature Request #55, "Allow double-quoted column names, column aliases,table names" ( http://sourceforge.net/p/csvjdbc/feature-requests/55/ ) that is included in the latest CsvJdbc version 1.0-17.

Column names including special characters can now be used with double-quotes around them in SQL statements. For example,

SELECT "[organisation_id]", "foo bar", "< 5 seconds" as HELLO FROM sample

This is the approach used by other databases such as PostgreSQL.

Upvotes: 2

Angelo Fuchs
Angelo Fuchs

Reputation: 9941

You must strip away the [] before you use them in your SQL. There are certain chars that are not supported in many (all I know of) SQL Systems.

So, you may not name a column [name]. There is no escape from that. You must remove it and use name instead.

(Please be aware that this only goes for table and column NAMES. in the CONTENT of a column, you must place quotes around the texts like "value with [] is ok")

After some read I can say that sometimes its allowed to use "" to escape column names as well, so "[organisation_id]" might sometimes be allowed.

Upvotes: 1

Related Questions