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.

score 4 · Answer 1 · answered Dec 06 '17 at 13:31

4

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)

answered Dec 06 '17 at 13:31

Thorsten

144
1
1
10

score 2 · Answer 2 · answered Jun 21 '17 at 17:15

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)

answered Jun 21 '17 at 17:15

Golden Ratio

349
2
14

score 2 · Answer 3 · edited Apr 30 '18 at 12:32

2

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

edited Apr 30 '18 at 12:32

Federico Navarrete

3,069
5
41
76

answered Apr 30 '18 at 11:53

Dick van Huizen

161
1
5

score 1 · Answer 4 · answered Jun 21 '17 at 17:14

1

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

answered Jun 21 '17 at 17:14

Alok Gupta

1,353
1
13
29

score 1 · Answer 5 · answered Jun 21 '17 at 17:39

1

You can try...

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

answered Jun 21 '17 at 17:39

mauro

5,730
2
26
25

Tried this too. The way R handles spaces in column. It gives out the same error. – Sripati Jun 21 '17 at 18:37

R ODBC - Querying Column name with spaces

5 Answers5