Question: I am writing a rails 3 application, and I have a database table of Users. The users have first\_name and last\_name columns. On my page that lists the users, I want to have a single text search box that allows the user to filter the list. They need to be able to type into the search box one of the following:
- A first name of a user
- A last name of a user
- A full name of a user (eg. "Smith, John")

I do not know how to write the where clause that will return these results. The closest I have gotten is below, however the concatenated fields part does not work.

```
where('(first_name LIKE :search) or (last_name LIKE :search) or ("#{last_name}, #{first_name}" LIKE :search)', :search => "%#{search}%")

```

I am also interested to know if there is a better way to handle my OR statements, as this clause is getting quite clunky.

Thanks for your help!

Answer: 
In case it helps anyone else, this is the solution I went with:

```
  def self.search(query)
    if query.blank?
      scoped
    else
      sql = query.split.map do |word|
       %w[first_name last_name].map do |column|
        sanitize_sql ["#{column} LIKE ?", "%#{word}%"]
       end.join(" or ")
      end.join(") and (")
      where("(#{sql})")
    end
  end

```

Thanks to Ryan Bates of Railscasts for this solution.
You must remember that the `where` will turn into SQL, and if you want to compare concatenated columns with a search-string, you have to do this at the database/sql level.

Now this is where it gets tricky, because concatenation in sql differs depending on your database.

In Postgresql and Oracle you would write something like:

```
where("(first_name LIKE :search) or (last_name LIKE :search) or (last_name || ',' || first_name LIKE :search)", :search => "%#{search}%"

```

Unfortunately, Mysql does not support the standard concatenation operator `||`, with mysql you will have to use `concat` instead:

```
where("(first_name LIKE :search) or (last_name LIKE :search) or (concat(last_name, ',', first_name) LIKE :search)", :search => "%#{search}%"

```