R ODBC - Querying Column name with spaces

Question

I am trying to query through R OBDC. But one Column name has space on it. For example, [Account No].

I am using this code to query:

esiid_ac <- sqlQuery(myconn, paste("
 SELECT * FROM CustomerUsage WHERE ((CustomerUsage.Account No ='", 12345, "')) ", sep=""),as.is=TRUE)

I am getting the following error:

[1] "42000 102 [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Incorrect syntax near 'No'." [2] "[RODBC] ERROR: Could not SQLExecDirect '\n SELECT * FROM CustomerUsage WHERE ((CustomerUsage.Account No ='678987')) '

How to solve this?

Can I read this table with column index instead of column names?

Thank you.

Answer 1

After tinkering around with quotes a little bit, this worked for me:

df <- sqlQuery(myconn, 'SELECT * FROM mytable WHERE "column name" =123', as.is=TRUE)

Answer 2

Have you tried square brackets (They work for me when there are special characters in column names)?

esiid_ac <- sqlQuery(myconn, paste(" SELECT * FROM CustomerUsage WHERE ((CustomerUsage.[Account No] ='", 12345, "')) ", sep=""),as.is=TRUE)

Answer 3

You can use \"COL_NAME\" instead of COL_NAME and use that as you would always use it. For example:

esiid_ac <- sqlQuery(myconn, "SELECT * FROM CustomerUsage WHERE \"Account No\" = 12345")