Searching for partial strings in Ecto using ilike

Question

I am trying to search in a database to see if a string matches part of another string in the database. I can get it to match if the two are exact using ilike, but when I am searching for just part of the string it does not catch data that contains it. Here is what my code looks like for the query:

    servicesstate = Repo.all(from p in Callme.Service, where: ilike(p.locations, ^zip.state))

It will match when the values are exact ("South Carolina", "South Carolina"), but I want it to match when it is something like ("Located in South Carolina", "South Carolina")

Thanks

Answer 1

You can use the % syntax for LIKE/ILIKE:

servicesstate = Repo.all(from p in Callme.Service, where: ilike(p.locations, ^"%#{zip.state}%"))

Note that this will not work correctly if zip.state contains a %. If it can contain %, you'll have to use Ecto.Query.API.fragment/1 with a query like this.