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]
