[ruby-on-rails] Case-insensitive search in Rails model

My product model contains some items

 Product.first
 => #<Product id: 10, name: "Blue jeans" >

I'm now importing some product parameters from another dataset, but there are inconsistencies in the spelling of the names. For instance, in the other dataset, Blue jeans could be spelled Blue Jeans.

I wanted to Product.find_or_create_by_name("Blue Jeans"), but this will create a new product, almost identical to the first. What are my options if I want to find and compare the lowercased name.

Performance issues is not really important here: There are only 100-200 products, and I want to run this as a migration that imports the data.

Any ideas?

This question is related to ruby-on-rails activerecord case-insensitive

The answer is


You might want to use the following:

validates_uniqueness_of :name, :case_sensitive => false

Please note that by default the setting is :case_sensitive => false, so you don't even need to write this option if you haven't changed other ways.

Find more at: http://api.rubyonrails.org/classes/ActiveRecord/Validations/ClassMethods.html#method-i-validates_uniqueness_of


In postgres:

 user = User.find(:first, :conditions => ['username ~* ?', "regedarek"])

Similar to Andrews which is #1:

Something that worked for me is:

name = "Blue Jeans"
Product.find_by("lower(name) = ?", name.downcase)

This eliminates the need to do a #where and #first in the same query. Hope this helps!


So far, I made a solution using Ruby. Place this inside the Product model:

  #return first of matching products (id only to minimize memory consumption)
  def self.custom_find_by_name(product_name)
    @@product_names ||= Product.all(:select=>'id, name')
    @@product_names.select{|p| p.name.downcase == product_name.downcase}.first
  end

  #remember a way to flush finder cache in case you run this from console
  def self.flush_custom_finder_cache!
    @@product_names = nil
  end

This will give me the first product where names match. Or nil.

>> Product.create(:name => "Blue jeans")
=> #<Product id: 303, name: "Blue jeans">

>> Product.custom_find_by_name("Blue Jeans")
=> nil

>> Product.flush_custom_finder_cache!
=> nil

>> Product.custom_find_by_name("Blue Jeans")
=> #<Product id: 303, name: "Blue jeans">
>>
>> #SUCCESS! I found you :)

Find_or_create is now deprecated, you should use an AR Relation instead plus first_or_create, like so:

TombolaEntry.where("lower(name) = ?", self.name.downcase).first_or_create(name: self.name)

This will return the first matched object, or create one for you if none exists.


Several comments refer to Arel, without providing an example.

Here is an Arel example of a case-insensitive search:

Product.where(Product.arel_table[:name].matches('Blue Jeans'))

The advantage of this type of solution is that it is database-agnostic - it will use the correct SQL commands for your current adapter (matches will use ILIKE for Postgres, and LIKE for everything else).


There are lots of great answers here, particularly @oma's. But one other thing you could try is to use custom column serialization. If you don't mind everything being stored lowercase in your db then you could create:

# lib/serializers/downcasing_string_serializer.rb
module Serializers
  class DowncasingStringSerializer
    def self.load(value)
      value
    end

    def self.dump(value)
      value.downcase
    end
  end
end

Then in your model:

# app/models/my_model.rb
serialize :name, Serializers::DowncasingStringSerializer
validates_uniqueness_of :name, :case_sensitive => false

The benefit of this approach is that you can still use all the regular finders (including find_or_create_by) without using custom scopes, functions, or having lower(name) = ? in your queries.

The downside is that you lose casing information in the database.


Case-insensitive searching comes built-in with Rails. It accounts for differences in database implementations. Use either the built-in Arel library, or a gem like Squeel.


This is a complete setup in Rails, for my own reference. I'm happy if it helps you too.

the query:

Product.where("lower(name) = ?", name.downcase).first

the validator:

validates :name, presence: true, uniqueness: {case_sensitive: false}

the index (answer from Case-insensitive unique index in Rails/ActiveRecord?):

execute "CREATE UNIQUE INDEX index_products_on_lower_name ON products USING btree (lower(name));"

I wish there was a more beautiful way to do the first and the last, but then again, Rails and ActiveRecord is open source, we shouldn't complain - we can implement it ourselves and send pull request.


Some people show using LIKE or ILIKE, but those allow regex searches. Also you don't need to downcase in Ruby. You can let the database do it for you. I think it may be faster. Also first_or_create can be used after where.

# app/models/product.rb
class Product < ActiveRecord::Base

  # case insensitive name
  def self.ci_name(text)
    where("lower(name) = lower(?)", text)
  end
end

# first_or_create can be used after a where clause
Product.ci_name("Blue Jeans").first_or_create
# Product Load (1.2ms)  SELECT  "products".* FROM "products"  WHERE (lower(name) = lower('Blue Jeans'))  ORDER BY "products"."id" ASC LIMIT 1
# => #<Product id: 1, name: "Blue jeans", created_at: "2016-03-27 01:41:45", updated_at: "2016-03-27 01:41:45"> 

Another approach that no one has mentioned is to add case insensitive finders into ActiveRecord::Base. Details can be found here. The advantage of this approach is that you don't have to modify every model, and you don't have to add the lower() clause to all your case insensitive queries, you just use a different finder method instead.


user = Product.where(email: /^#{email}$/i).first

Upper and lower case letters differ only by a single bit. The most efficient way to search them is to ignore this bit, not to convert lower or upper, etc. See keywords COLLATION for MSSQL, see NLS_SORT=BINARY_CI if using Oracle, etc.


Quoting from the SQLite documentation:

Any other character matches itself or its lower/upper case equivalent (i.e. case-insensitive matching)

...which I didn't know.But it works:

sqlite> create table products (name string);
sqlite> insert into products values ("Blue jeans");
sqlite> select * from products where name = 'Blue Jeans';
sqlite> select * from products where name like 'Blue Jeans';
Blue jeans

So you could do something like this:

name = 'Blue jeans'
if prod = Product.find(:conditions => ['name LIKE ?', name])
    # update product or whatever
else
    prod = Product.create(:name => name)
end

Not #find_or_create, I know, and it may not be very cross-database friendly, but worth looking at?


An alternative can be

c = Product.find_by("LOWER(name)= ?", name.downcase)

Assuming that you use mysql, you could use fields that are not case sensitive: http://dev.mysql.com/doc/refman/5.0/en/case-sensitivity.html


You can also use scopes like this below and put them in a concern and include in models you may need them:

scope :ci_find, lambda { |column, value| where("lower(#{column}) = ?", value.downcase).first }

Then use like this: Model.ci_find('column', 'value')


If you are using Postegres and Rails 4+, then you have the option of using column type CITEXT, which will allow case insensitive queries without having to write out the query logic.

The migration:

def change
  enable_extension :citext
  change_column :products, :name, :citext
  add_index :products, :name, unique: true # If you want to index the product names
end

And to test it out you should expect the following:

Product.create! name: 'jOgGers'
=> #<Product id: 1, name: "jOgGers">

Product.find_by(name: 'joggers')
=> #<Product id: 1, name: "jOgGers">

Product.find_by(name: 'JOGGERS')
=> #<Product id: 1, name: "jOgGers">

Examples related to ruby-on-rails

Embed ruby within URL : Middleman Blog Titlecase all entries into a form_for text field Where do I put a single filter that filters methods in two controllers in Rails Empty brackets '[]' appearing when using .where How to integrate Dart into a Rails app Rails 2.3.4 Persisting Model on Validation Failure How to fix "Your Ruby version is 2.3.0, but your Gemfile specified 2.2.5" while server starting Is the server running on host "localhost" (::1) and accepting TCP/IP connections on port 5432? Rails: Can't verify CSRF token authenticity when making a POST request Uncaught ReferenceError: React is not defined

Examples related to activerecord

Empty brackets '[]' appearing when using .where Get only records created today in laravel How do you manually execute SQL commands in Ruby On Rails using NuoDB Rails 4 LIKE query - ActiveRecord adds quotes Rails create or update magic? CodeIgniter query: How to move a column value to another column in the same row and save the current time in the original column? Check if record exists from controller in Rails Codeigniter's `where` and `or_where` Codeigniter LIKE with wildcard(%) Codeigniter: does $this->db->last_query(); execute a query?

Examples related to case-insensitive

Are PostgreSQL column names case-sensitive? How to make String.Contains case insensitive? SQL- Ignore case while searching for a string String contains - ignore case How to compare character ignoring case in primitive types How to perform case-insensitive sorting in JavaScript? Contains case insensitive Case insensitive string as HashMap key Case insensitive searching in Oracle How to replace case-insensitive literal substrings in Java