CODEWITHSUNDEEP
ruby-on-railspostgresqlactiverecord
bigpotato
bigpotato

Reputation: 27557

Rails + ActiveRecord + Postgres: How to match case insensitive substring?

I have to look for all emails from the User model that have the substring "cpg". So it would match "[email protected]", "[email protected]", etc

I'm pretty sure how to do case insensitive (using User.where("lower(email)...") but I don't know how to find a substring.

I'm using Rails 3 + Postgres 9.x

Upvotes: 6

Views: 7522

Answers (4)

user198026
user198026

Reputation: 254

Option 1:

User.where('LOWER(email) = LOWER(?)', search_email)

Option 2: Use Postgres' citex extension which will do it for you (as answered above).

Upvotes: 2

Tyler Rick
Tyler Rick

Reputation: 9521

If you don't want to have to always remember to call lower(email) (and input.downcase on your input in Ruby) whenever you search on your email field, you can make the email column itself case insensitive by using the citext data type.

That's what I just did. I created a migration like this:

class ChangeUsersEmailToCitext < ActiveRecord::Migration
  def up
    # Rails 4:
    #enable_extension("citext")
    # Rails 3:
    execute 'create extension citext'

    change_table :users do |t|
      t.change :email, :citext
    end
  end
end

And now I no longer have to do any extra effort to make the queries case insensitive! It's all handled automatically for me behind the scenes now.

This uses PostgreSQL's citext extension.

http://www.sfcgeorge.co.uk/posts/2013/11/12/case-insensitive-usernames-with-postgres has a nice article about this.

Upvotes: 10

ifightcrime
ifightcrime

Reputation: 1302

Actually, you can do this in Rails agnostically: http://robb.weblaws.org/2013/12/05/yes-rails-supports-case-insensitive-database-queries/

Upvotes: 6

sevenseacat
sevenseacat

Reputation: 25049

Nothing in Rails to do it, you can just use PostgreSQL's ilike (case insensitive like function).

Like so: User.where("email ilike '%cpg%'")

Upvotes: 16

Related Questions