What is the correct syntax for Regex in Ecto Query?

Question

None of these works:

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

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

What is the correct syntax? I can not find anything in the docs.

Answer 1

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

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

like/2 will be used for case sensitive searches.

Ecto does not support regex out of the box because implementations vary widely between databases. You will need to study the regular expression syntax for the database you are targeting and create this part of the query yourself using Ecto fragment/1 . The following example, which searches for all models that match /^Peter [AZ]$/ , uses the PostgeSQL POSIX regex feature :

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