SQL query in R using concatenation methods

Question

I tried using this query below in concatenation that I found in this forum as well:

DataFrame <- sqldf("select FirstName, LastName, MiddleName, FirstName || ' ' || MiddleName || ' ' || LastName as FullName from People")

Unfortunately if for example, MiddleName has no data or NA in the row, FullName also becomes NA despite the fact there is a LastName and FirstName. Same goes with the other columns.

Answer 1

Stealing from this previous comment here - String concatenation does not work in SQLite - this is really an SQLite issue, rather than an sqldf issue.

Since NULL || "text" returns NULL but '' || "text" returns '' you need to replace with blank text using coalesce() or ifnull():

library(sqldf)

People <- data.frame(
  FirstName=c("Joe","Dave", "John"),
  MiddleName=c(NA,"Alan", ""),
  LastName=c("Bloggs","Davies","Smith")
)


sqldf("
  select
    FirstName,
    LastName,
    MiddleName,
    FirstName || ' ' || MiddleName || ' ' || LastName as FullName,
    FirstName || ' ' || coalesce(MiddleName,'') || ' ' || coalesce(LastName,'') as FullFix
  from People
")

#  FirstName LastName MiddleName         FullName          FullFix
#1       Joe   Bloggs       <NA>             <NA>      Joe  Bloggs
#2      Dave   Davies       Alan Dave Alan Davies Dave Alan Davies
#3      John    Smith                 John  Smith      John  Smith