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]
