Rails & MySQL - How do I use the LIKE operator with % in find_by_sql in rails?

Question

How do I remove the quotation marks around a variable so that I can use the LIKE operator in find_by_sql in rails?

@entries1 = Entry.find_by_sql(["SELECT `entries`.name as name FROM `entries` where `entries`.name like '%?%'",@something])

will yield

SELECT `entries`.name as name FROM `entries` where `entries`.name like '%'hello'%'

when @something = 'hello'

Answer 1

Instead of putting the percent symbols in the SQL, add them to the variable you're injecting (after escaping percent signs/underscores already present!)

"%#{@something.gsub('%', '\%').gsub('_', '\_')}%"

Answer 2

You could use concat inside the database:

Entry.find_by_sql([
  "SELECT ... `entries`.name like concat('%', ?, '%')",
  @something
])

or you can add the wildcards in Ruby:

Entry.find_by_sql([
  "SELECT ... `entries`.name like ?",
  "%#{@something}"
])

Note that other databases will want to see '%' || ? || '%' or '%' + ? + '%' instead of the concat call.

Answer 3

@entries1 = Entry.select('name').where('name like ?',"%#{@something}%"]) should do the same

Answer 4

You need four '%' around the search value and must precede each with delimiter ‘\’.

For example search_val can be “” or “031”, etc...

find_by_sql([ "select  * from tableX where id like '\%\%#{search_val}\%\%'" ])