Querying case insensitive values in Ecto

Questions / Help
,
1

I want to find a value in the database without considering case sensitivity. For example I have a value msg = “Join” in my DB …I want to find the exact value even if I search for it like this

select * where msg = 'JoiN'
select * where msg = 'JoiN'
select * where msg = 'joiN'
select * where msg = 'jOIN'

here is the query i wrote

def is_valid_msg?(msg) do
    message
    |> where([a], fragment("charindex(lower(?), lower(?)) > 0", ^remove_spaces(msg), a.text) and a.status == "A")
    |> select([:id, :msg])

    |> Repo.one()

  end

  def remove_spaces(msg) do
    msg
    |> String.replace(~r/ +/, " ")
    |> String.trim()
  end
2 
message
|> where([a], fragment("lower(?) = ?", a.text, ^String.downcase(msg)))

or

fragment("lower(?) = lower(?)", a.text, ^msg)

Also, you can use citext type if you want to avoid fragments and simply use = operator.

3 Likes
3

Exactly as @fuelen mentioned. For a bit more info, the docs for fragment use lower in the example.

In cases where case-insensitivity is necessary in all contexts, I opt for a citext type.

4

Thanks worked well