Message-ID: <20041125112505.B21088@jessie.research.bell-labs.com>
Date: 2004-11-25T16:25:05Z
From: David James
Subject: Searching for a string in RSQLite
In-Reply-To: <fbubq0984n88mh6eb4aq12pdk3jfmgjbfg@4ax.com>; from murdoch@stats.uwo.ca on Thu, Nov 25, 2004 at 10:37:49AM -0500
Single quotes in a string are escaped by putting two single quotes in
a row. E.g.,
state <- "mess: '' ("
Regards,
--
David
Duncan Murdoch wrote:
> 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