converting numeric to character in sqldf

Question

I am trying to concatenate 2 columns of a data-frame in R using sqldf command. However, it treats them as numeric and sums them up. I looked at the solution to a similar question on stackoverflow but it still doesn't work for me.

Here's what my DF looks like:

SP1  SP2
521  526
521  523

What I want is:

SP
521-526
521-523

I tried the following:

sqldf("select  SP1 + '-' + SP2 as SP from DF")

I also tried:

DF2 <- transform(DF, SP1 = as.character(SP1), SP2 = as.character(SP2)))
sqldf("select  SP1 + '-' + SP2 as SP from DF2")

In both cases, the result I get is

SP
1047
1044

Any thoughts?

score 3 · Accepted Answer · answered Jul 27 '15 at 16:01

3

Although that will work in SQL Server, you will have to use || instead of + since sqldf is most likely using a SQLite backend:

R> sqldf::sqldf(
    "SELECT SP1 || '-' || SP2 AS SP
     FROM df;"
  )
       SP
1 521-526
2 521-523

Data:

df <- read.table(
  text = "SP1  SP2
  521  526
  521  523",
  header = TRUE
)

answered Jul 27 '15 at 16:01

nrussell

18,382
4
47
60

Thank you nrussell. It works with just a minor addition. In fact cast does not work and before your command, I should convert these columns to character as below: DF <- transform(DF, SP1 = as.character(SP1), SP2 = as.character(SP2))) – asiehh Jul 27 '15 at 16:17
I have `options(stringsAsFactors = FALSE)` as a default setting on my machine, in while case the above answer works fine. – nrussell Jul 27 '15 at 16:18
It depends on whether the dataframe is has the values as integer or numeric. If numeric, you get spurious `.0`s. – James Jul 27 '15 at 16:21

score 0 · Answer 2 · answered Jul 28 '15 at 16:31

If the RH2 package is loaded sqldf will use H2 by default, and we can access other functions not available in SQLite:

In this example we can use CONCAT:

library(RH2)
library(sqldf
sqldf("SELECT CONCAT(SP1, '-' , SP2) AS SP FROM df")

Output:

      SP
1 521-526
2 521-523

If we need to convert columns to character:

# Using sqldf alone   
sqldf("SELECT CAST(SP1 as text) FROM df")
# RH2 loaded
sqldf("SELECT TO_CHAR(SP1) FROM df")

converting numeric to character in sqldf

2 Answers2