yes in therory. But practicaly, you always have business rules and data knowledge without what you can do nothing.
so the substring must be constructed according to data. Mathias Selon Ben Kutsch <[EMAIL PROTECTED]>: > 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]
