sorry for the first select (bad copy of a string 'lastname'):
mysql> select concat(firstname,' ',lastname) from names where concat(firstname,'
',lastname) like 'Jean Dupond%';
+--------------------------------+
| concat(firstname,' ',lastname) |
+--------------------------------+
| Jean Dupond |
+--------------------------------+
1 row in set (0.00 sec)
mathias
Selon [EMAIL PROTECTED]:
> 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]
