CODEWITHSUNDEEP
elixirphoenix-frameworkecto
user2480169
user2480169

Reputation: 151

Searching for partial strings in Ecto using ilike

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

Upvotes: 12

Views: 6071

Answers (1)

Dogbert
Dogbert

Reputation: 222348

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.

Upvotes: 21

Related Questions