the substring will only work as long as you don't have spaces in the first and last name columns 'Billy Ray' Smith and John 'Von Hoenhiem' would cause problems
-----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Thursday, June 23, 2005 4:05 PM To: Matt Babineau Cc: mysql@lists.mysql.com Subject: Re: How to SELECT something (CONCAT) and search the field Hi, what's your version ? in 4.11 the two forms work : mysql> select concat(firstname,' ','lastname') from names; +----------------------------------+ | concat(firstname,' ','lastname') | +----------------------------------+ | Jean lastname | +----------------------------------+ 1 row in set (0.00 sec) mysql> select firstname,lastname from names where concat(firstname,' ','lastname') like '%J%'; +-----------+----------+ | firstname | lastname | +-----------+----------+ | Jean | Dupond | +-----------+----------+ 1 row in set (0.00 sec) mysql> select firstname,lastname from names where concat(rtrim(ltrim(firstname)),' ',lastname) like '%Jean Dupond%'; +-----------+----------+ | firstname | lastname | +-----------+----------+ | Jean | Dupond | +-----------+----------+ 1 row in set (0.02 sec) mysql> select firstname,lastname from names where concat(firstname,' ',lastname) like 'Jean Dupond'; +-----------+----------+ | firstname | lastname | +-----------+----------+ | Jean | Dupond | +-----------+----------+ 1 row in set (0.00 sec) BUT i Think that : ********************* to use index on firstname or lastname, it's better to split $user rather than concat the two columns : mysql> select firstname,lastname from names where firstname like substring_index('%Jean Dupond%',' ',1); +-----------+----------+ | firstname | lastname | +-----------+----------+ | Jean | Dupond | +-----------+----------+ 1 row in set (0.00 sec) mysql> select firstname,lastname from names where lastname like substring_index('%Jean Dupond%',' ',-1); +-----------+----------+ | firstname | lastname | +-----------+----------+ | Jean | Dupond | +-----------+----------+ 1 row in set (0.00 sec) Mathias Selon Matt Babineau <[EMAIL PROTECTED]>: > Hey All- > > Got a fun question - I hit the manual but not much luck on my question. I > want to combine 2 fields and then search them.... > > SELECT first_name, lastname FROM user WHERE CONCAT(first_name, ' ', > last_name) LIKE '%$user%' > > Does this make sense? The CONCAT function was the closest I found to try and > do what I want to do. I alread tried this: > > SELECT concat(first_name, ' ', last_name) as fullname FROM user... > > This did not work. If anyone has any ideas on how to search for users when > the first_name and last_name fields are broken up I'm all ears! > > > Thanks, > > Matt Babineau > Criticalcode > 858.733.0160 > [EMAIL PROTECTED] > http://www.criticalcode.com > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
