How to use SQL query 'LIKE' with SQL Interpolate in R?

Question

I am writing an application in R that makes parameterized queries and sends them to a database to return the applicable information to a data table in the application. Because of the nature of a search query, I need to use the SQL term LIKE, with % signs around the search term. And to protect the database from SQL injection, I need to use the sqlInterpolate function. But I'm having issues with the way the sqlInterpolate function is making the query.

Right now, when I go into the R console, this works:

> sql <- sqlInterpolate(conn, "SELECT * FROM table WHERE Column1 LIKE '%000g7%'")
> dbGetQuery(conn, sql)

But this doesn't,

> str <- "000g7"
> sql <- sqlInterpolate(conn, "SELECT * FROM table WHERE Column1 LIKE '%?search%'", search = str)
> dbGetQuery(conn, sql)

it just returns an empty list. If I could get some help with the syntax of the query, I'd greatly appreciate it. I'm almost positive it has something to do with the apostrophes or something along those lines. Or if there is a better way to do this, I'm all ears.

What you wrote is definitely the way you would do this in most other programming languages. I can't test locally because I don't even know which package the OP is using. — Tim Biegeleisen
– Tim Biegeleisen, Commented Oct 7, 2019 at 15:04

MrFlick · Accepted Answer · 2019-10-07 15:12:32Z

4

In the case of the LIKE operator, the % are really part of the string you are searching with, so you just need to add those to the value you are searching for before you interpolate the value into the query. You should use

str <- "000g7"
sql <- sqlInterpolate(conn, "SELECT * FROM table WHERE Column1 LIKE ?search", 
  search = paste0("%", str, "%"))

answered Oct 7, 2019 at 15:12

MrFlick

209k19 gold badges300 silver badges324 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to use SQL query 'LIKE' with SQL Interpolate in R?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related