How to perform a case insensitive search in a Rails app?

Question

in a Rails 3.2 app I have a search field which draws on the following routine in the user model.

def self.search(search)
   if search
        where('fullname LIKE ? OR email LIKE ?', "%#{search}%", "%#{search}%")
   else
        scoped
   end
end

This appears to be performing a case sensitive search. How can I modify this to so that it is not case sensitive?

Many thanks

Rails can do this for you. Use the [built-in Arel library or a gem like Squeel](http://stackoverflow.com/questions/2220423/case-insensitive-search-in-rails-model). — Dogweather, Dec 06 '13 at 20:57

score 5 · Accepted Answer · answered Mar 01 '12 at 10:16

5

If you're using Postgres you can use 'ilike':

def self.search(search)
  if search
    where('fullname ILIKE ? OR email ILIKE ?', "%#{search}%", "%#{search}%")
  else
    scoped
  end
end

answered Mar 01 '12 at 10:16

Bradley Priest

7,368
1
26
33

Thanks @bradley. I am using Postges, and this worked a treat. Am I right in thinking that LIKE is case insensitive on other db platforms? I don't recall running into this before. – Andy Harvey Mar 01 '12 at 10:26
LIKE is case sensitive in MySQL, at least. – Waynn Lue Mar 03 '12 at 03:53

score 0 · Answer 2 · answered Mar 01 '12 at 10:13

0

When I've done this in the past, I've used the MySQL routine UPPER. So something like

def self.search(search)
   if search
        where('UPPER(fullname) LIKE ? OR UPPER(email) LIKE ?', upcase(search), upcase(search))
   else
        scoped
   end
end

answered Mar 01 '12 at 10:13

Waynn Lue

11,048
7
49
71

How to perform a case insensitive search in a Rails app?

2 Answers2