[R] Searching for a string in RSQLite

[Dr Mike Waters]

> -----Original Message-----
> From: r-help-bounces at stat.math.ethz.ch 
> [mailto:r-help-bounces at stat.math.ethz.ch] On Behalf Of Duncan Murdoch
> Sent: 25 November 2004 15:38
> To: r-help at stat.math.ethz.ch
> Subject: [R] Searching for a string in RSQLite
> 
> 
> I'd like to search for a particular string in an SQLite 
> database using RSQLite, but I'm running into problems 
> constructing the query properly, because of embedded quotes 
> and parens in the string.
> 
> Is there a function that escapes these for me, or some other 
> fixup that would let me do the queries below?  In the real 
> situation I don't have control over what strings get searched for.
> 
> Example based on ?SQLite:
> 
> > library(RSQLite)
> > m <- dbDriver("SQLite")
> > con <- dbConnect(m, dbname = "base.dbms")
> > data(USArrests)
> > dbWriteTable(con, "USArrests", USArrests, overwrite = T)
> [1] TRUE
> > state <- "Wyoming"
> 
> # this works fine:
> 
> > dbGetQuery(con, paste("SELECT * from USArrests where 
> > row_names='",state,"'",sep=""))
>   row_names Murder Assault UrbanPop Rape
> 1   Wyoming    6.8     161       60 15.6
> 
> # Buf if the search string contains characters that SQL 
> interprets, I # get an error
> 
> > state <- "messy:  ' ("
> > dbGetQuery(con, paste("SELECT * from USArrests where 
> > row_names='",state,"'",sep=""))
> Error in sqliteExecStatement(con, statement) : 
>         RS-DBI driver: (error in statement: near "(": syntax error)
> 
> Duncan Murdoch
> 
> ______________________________________________
> R-help at stat.math.ethz.ch mailing list 
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide! 
> http://www.R-project.org/posting-guide.html
> 

The normal character for escaping the next character to prevent it being
interpreted in SQL (including SQLite) is the backslash (i.e. \). Unless, of
course, I'm not understanding the precise nature of your request.

Regards

Mike