CODEWITHSUNDEEP
sqlrrodbc
Sripati
Sripati

Reputation: 71

R ODBC - Querying Column name with spaces

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.

Upvotes: 5

Views: 2495

Answers (5)

Dick van Huizen
Dick van Huizen

Reputation: 191

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")

Upvotes: 2

Thorsten
Thorsten

Reputation: 144

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)

Upvotes: 4

mauro
mauro

Reputation: 5940

You can try...

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

Upvotes: 1

Golden Ratio
Golden Ratio

Reputation: 349

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)

Upvotes: 2

Alok Gupta
Alok Gupta

Reputation: 1360

Can you try to put the column name like [Account No] and then try?

Upvotes: 1

Related Questions