What's the correct syntax for Regex in an Ecto Query?

Question

Neither of these work:

from m in Model, where: m.name == ^~r(/.*#{query}.*/i)

from m in Model, where: m.name =~ ^~r(/.*#{query}.*/i)

What's the correct syntax? I can't find anything in the docs.

Answer 1

Since you're trying to match a case insensitive phrase, you should use ilike/2 which uses the SQL LIKE operator:

from m in Model, where: ilike(m.name, "%#{query}%")

like/2 would be used for a case sensitive search.

Ecto doesn't support regex out of the box because the implementations vary wildly between databases. What you'll need to do is look into the regular expression syntax for the database you're targeting and build that part of the query yourself using an Ecto fragment/1. The following example, which searches for all models that match /^Peter [A-Z]$/, uses PostgreSQL's POSIX regex feature:

from m in Model, where: fragment('? ~ ?', m.name, '^Peter [A-Z]$')