1

The following Employee table is from http://www.analyticsvidhya.com/blog/2015/12/sql-commands-common-excel-operations/. The code that follows the table is based on Example 8B.

Employee <- structure(list(ECODE = c("A011", "A001", "A007"), DOJ = 
c("3-Jul-12", "12-Jun-12", "13-Aug-12"), Experience = c(2.1, 2.2, 2),
Gender = c("Male", "Male", "Female"), Department = c("Support", "Admin", 
"Support"), No_Of_Hours = c(17.42, 15.45, 13.54), Pay_Per_Hours = c(40L, 
45L, 44L), Total_Payout = c(696.8, 695.25, 595.72), City = c("Delhi", 
"Delhi", "Mumbai")), .Names = c("ECODE", "DOJ", "Experience", "Gender", 
"Department", "No_Of_Hours", "Pay_Per_Hours", "Total_Payout", "City"), 
row.names = c(NA, -3L), class = c("data.table", "data.frame"))

# Employee 
#     ECODE DOJ       Experience Gender  Department    No_Of_Hours 
# 1:  A011  3-Jul-12  2.1        Male    Support       17.42 
      Pay_Per_Hours
      40
# 2:  A001  12-Jun-12 2.2        Male    Admin         15.45            
      45
# 3:  A007  13-Aug-12 2.0        Female  Support       13.54            
      44
#     Total_Payout   City
# 1:  696.80         Delhi
# 2:  695.25         Delhi
# 3:  595.72         Mumbai

In Example 8B [LEFT(x,N)] is used, but I found out that Leftstr (how to use right/left to split a variable in sqldf, as in left(x,n)) is the way it is done in sqldf.

Since there is not a special character (".") in the column name, the operation works as expected.

sqldf("Select *, Leftstr(City,3) as 'City_Code' from Employee where
Department = 'Admin'")

#    ECODE  DOJ         Experience  Gender   Department  No_Of_Hours
# 1  A001   12-Jun-12   2.2         Male     Admin       15.45     
#    Pay_Per_Hours  Total_Payout  City   City_Code
#    45             695.25        Delhi  Del

In the example below, which is similar to my real data set, there is a period in the column name and the answer is not expected.

sqldf("Select *, Leftstr('City.1',3) as 'City_Code' from Employee where 
Department = 'Admin'")

#    ECODE  DOJ         Experience  Gender   Department  No_Of_Hours
# 1  A001   12-Jun-12   2.2         Male     Admin       15.45     
#    Pay_Per_Hours  Total_Payout  City   City_Code
#    45             695.25        Delhi  Cit

What needs to be done to the sqldf code when there is a special character in the column name for leftstr operations?

Community
  • 1
  • 1
iembry
  • 962
  • 1
  • 7
  • 23
  • Possible duplicate of [Error: No Such Column using SQLDF](http://stackoverflow.com/questions/23451139/error-no-such-column-using-sqldf) – zx8754 Jul 20 '16 at 10:49

1 Answers1

1

Single quotes surround constants. To refer to a column name surround it with double quotes or with square brackets. Assuming that the City column is named City.1 instead:

sqldf("Select *, Leftstr([City.1],3) as 'City_Code' from Employee where 
  Department = 'Admin'")

giving:

  ECODE       DOJ Experience Gender Department No_Of_Hours Pay_Per_Hours
1  A001 12-Jun-12        2.2   Male      Admin       15.45            45
  Total_Payout City.1 City_Code
1       695.25  Delhi       Del
G. Grothendieck
  • 254,981
  • 17
  • 203
  • 341