> -----Original Message----- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of Duncan Murdoch > Sent: 25 November 2004 15:38 > To: [EMAIL PROTECTED] > 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 > > ______________________________________________ > [EMAIL PROTECTED] 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 ______________________________________________ [EMAIL PROTECTED] mailing list https://stat.ethz.ch/mailman/listinfo/r-help PLEASE do read the posting guide! http://www.R-project.org/posting-guide.html
