# Multi-column search in MySQL **URL:** \ **Category:** Rails\ **Created:** [January 6, 2010, 3:44pm UTC](https://www.ruby-forum.com/t/multi-column-search-in-mysql/181159 "2010-01-06T15:44:06Z")\ **Posts on this page:** 6\ **Page:** 1
**Author:** ![craigrails](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/c2a13f/32.png) [@craigrails](https://www.ruby-forum.com/u/craigrails)\ **Post date:** [January 6, 2010, 3:44pm UTC](https://www.ruby-forum.com/t/multi-column-search-in-mysql/181159/1 "2010-01-06T15:44:06Z")
I could handle this fairly easily in PostgreSQL but for this project, I am using MySQL… Postgres, ``` @members = Member.find(:all, :conditions => ["LOWER(first_name||last_name) LIKE ?", '%' + params[:member][:name].downcase + '%']) ``` MySQL ? ``` @members = Member.find(:all, :conditions => ["first_name LIKE ? OR last_name LIKE ?", "%" + params[:member][:name].downcase + "%", "%" + params[:member][:name].downcase + "%"]) ``` Apparently MySQL is case insensitive search by default which is fine. But the only thing I can figure out is that I have to search each field separately which seems rather crazy to me. I don’t know that this matters but this is for auto\_complete type function. Anyone have a clue stick for me? Craig ---
**Author:** ![craigrails](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/c2a13f/32.png) [@craigrails](https://www.ruby-forum.com/u/craigrails)\ **Post date:** [January 6, 2010, 6:35pm UTC](https://www.ruby-forum.com/t/multi-column-search-in-mysql/181159/2 "2010-01-06T18:35:34Z")
Craig W. wrote: > I don’t know that this > matters but this is for auto\_complete type function. > > Anyone have a clue stick for me? Don’t reinvent the wheel! There are many auto-complete plugins out there already. One of them may do precisely what you need. ---
**Author:** ![craigrails](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/c2a13f/32.png) [@craigrails](https://www.ruby-forum.com/u/craigrails)\ **Post date:** [January 6, 2010, 6:43pm UTC](https://www.ruby-forum.com/t/multi-column-search-in-mysql/181159/3 "2010-01-06T18:43:05Z")
Craig W. wrote: > I could handle this fairly easily in PostgreSQL but for this project, I > am using MySQL… > > Postgres, > > ``` > @members = Member.find(:all, > :conditions => ["LOWER(first_name||last_name) LIKE ?", > '%' + params[:member][:name].downcase + '%']) > > ``` > > MySQL ? > > ``` > @members = Member.find(:all, > :conditions => ["first_name LIKE ? OR last_name LIKE ?", > "%" + params[:member][:name].downcase + "%", > "%" + params[:member][:name].downcase + "%"]) > > ``` > > Apparently MySQL is case insensitive search by default which is fine. > But the only thing I can figure out is that I have to search each field > separately which seems rather crazy to me. MySQL departs from the SQL standard by using concat() instead of ||, or you can turn on ANSI\_mode to use ||. > I don’t know that this > matters but this is for auto\_complete type function. > > Anyone have a clue stick for me? I agree with Aldric: don’t reinvent the wheel. > Craig Best, –Â Marnen Laibow-Koser [http://www.marnen.org](http://www.marnen.org) removed\_email\_address@domain.invalid ---
**Author:** ![craigrails](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/c2a13f/32.png) [@craigrails](https://www.ruby-forum.com/u/craigrails)\ **Post date:** [January 6, 2010, 7:38pm UTC](https://www.ruby-forum.com/t/multi-column-search-in-mysql/181159/4 "2010-01-06T19:38:14Z")
On Wed, 2010-01-06 at 18:43 +0100, Marnen Laibow-Koser wrote: > > MySQL ? > > MySQL departs from the SQL standard by using concat() instead of ||, or > > you can turn on ANSI\_mode to use ||. > > > I don’t know that this > > matters but this is for auto\_complete type function. > > > > Anyone have a clue stick for me? > > I agree with Aldric: don’t reinvent the wheel. * * * I am using the plugin but I want to search for the string in either the first\_name or last\_name columns. It appears that the CONCAT function is not useful for me in this case… > > Member.find(:all, :conditions =\> [“CONCAT(first\_name, last\_name) > > LIKE ?”, “cra”]) > > =\> [] and should have returned a single result. Craig ---
**Author:** ![craigrails](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/c2a13f/32.png) [@craigrails](https://www.ruby-forum.com/u/craigrails)\ **Post date:** [January 6, 2010, 8:01pm UTC](https://www.ruby-forum.com/t/multi-column-search-in-mysql/181159/5 "2010-01-06T20:01:39Z")
On 06/01/2010 6:36 PM, Craig W. wrote: > > > ``` > > > :conditions => ["LOWER(first_name||last_name) LIKE ?", > > > > > > ``` > > > > > > But the only thing I can figure out is that I have to search each field > > > separately which seems rather crazy to me. @members = Member.find(:all, :conditions =\> [“LOWER(CONCAT(first\_name,last\_name)) LIKE ?”, ‘%’ + params[:member][:name].downcase + ‘%’]) This will behave identically to your postgres example as you are adding the two fields together and then comparing with the search string as a single field. Cheers, Gary. ---
**Author:** ![craigrails](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/c2a13f/32.png) [@craigrails](https://www.ruby-forum.com/u/craigrails)\ **Post date:** [January 7, 2010, 10:45pm UTC](https://www.ruby-forum.com/t/multi-column-search-in-mysql/181159/6 "2010-01-07T22:45:45Z")
On Wed, 2010-01-06 at 18:59 +0000, Gary D. wrote: > > > > ``` > > > > @members = Member.find(:all, > > > > > > > > ``` > > > > > > > > Apparently MySQL is case insensitive search by default which is fine. > > > > This will behave identically to your postgres example as you are adding the two fields together and then comparing with the search string as a single field. * * * indeed - perfect - thanks Craig