How to specify escape character for arel_table?

Question

I have this SQL query which works:

SELECT  DISTINCT "SIGN_UPS"."EMAIL" FROM "SIGN_UPS" WHERE (LOWER("SIGN_UPS"."EMAIL") LIKE 'amado\_bartell' ESCAPE '\');

But it only works with ESCAPE '\', without that it returns nothing.

My rails code is:

where arel_table['email'].lower.matches(sanitize_sql_like("amado_bartell")).to_sql

This produces everything in the SQL except for the escape—and I don't know how to get it to include the escape clause. How can I get arel table to define the escape character?

Answer 1

The source code for matches is:

def matches other, escape = nil, case_sensitive = false
  Nodes::Matches.new self, quoted_node(other), escape, case_sensitive
end

So by adding a second argument, you can specify an escape clause:

where arel_table['email'].lower.matches(sanitize_sql_like("amado_bartell"), '\\').to_sql