SQL recipe : column with special characters

emmamilliot
Level 2
SQL recipe : column with special characters

Hello Dataiku community,

I am trying to select a column from a table with a SQL query. But my column name is composed of a special character such as '?'. 

So I tried to select it with these methods: table.[columname_?] and table."columname_?" (methods used for SQL) . But it didn't work...

Have you faced this problem?

Thanks a lot.


Operating system used: Microsoft windows

0 Kudos
5 Replies
AlexT
Dataiker

Hi @emmamilliot ,

Have you tried using backticks `columname_?`. 

Also please confirm what database you are using( e.g  Postgres, MSSQL , Oracle?) 

Thanks,

0 Kudos
emmamilliot
Level 2
Author

Hello @AlexT

Yes, I have tried those... And also the \" columnname_?\" but none of these are working...

I am using a SQL server database.

Thank you. 

0 Kudos
AlexT
Dataiker

What error are you seeing exactly in the SQL Recipe when you try to use square brackets?

Also, what version of SQL servers are you running on? 

Have also tried to SET QUOTED_IDENTIFIER ON ? 

https://docs.microsoft.com/en-us/sql/t-sql/statements/set-quoted-identifier-transact-sql?view=sql-se...

https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms176027(v=sql.105)?redire... 

0 Kudos
emmamilliot
Level 2
Author

Hello @AlexT,

The error on the validation is "Query failed: A processing error "Invalid column name 'Opco/Central_null'." occurred."  The name of the column is Opco/Central_? , and it was between square brackets.

The version is  : Microsoft SQL Server 2016 (SP2-CU15) (KB4577775) - 13.0.5850.14 (X64) Sep 17 2020 22:12:45 Copyright (c) Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ). 

I tried with SET QUOTED_IDENTIFIER ON but no change...

Thank you for your help.

0 Kudos
Jurre
Level 5

Maybe a quick&dirty solution can be to be less specific with selecting, as removing unwanted data is a lot easier. The Like operator works with column names when you select them from information_schema.columns . I have not tested this, possibly you need to specify the table. Just my two cents. 

0 Kudos